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;
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;
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;
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.