How to write a validation procedure for three conditions which are dependent on each other .
first condition will be for country zone in that there will be condition for branch for specific date range .
Suffice to say you havent provided a lot of detail :-) but here's an example using a table
SQL> create table val_rules
2 ( country varchar2(10),
3 branch int,
4 date_from date,
5 date_to date
6 );
Table created.
SQL>
SQL> insert into val_rules values ('AUS',1, date '2016-01-01', date '2017-01-01');
1 row created.
SQL> insert into val_rules values ('AUS',2, date '2015-01-01', date '2017-01-01');
1 row created.
SQL> insert into val_rules values ('ENG',3, date '2018-01-01', date '2019-01-01');
1 row created.
SQL>
SQL> create or replace
2 procedure validator(p_country varchar2, p_branch int, p_dte date ) is
3 l_is_valid int;
4 begin
5 select count(*)
6 into l_is_valid
7 from val_rules
8 where country = p_country
9 and branch = p_branch
10 and date_from <= p_dte
11 and date_to >= p_dte;
12
13 if l_is_valid = 0 then
14 raise_application_error(-20000,'Invalid data');
15 end if;
16 end;
17 /
Procedure created.
SQL>
SQL> exec validator('AUS',1,sysdate);
PL/SQL procedure successfully completed.
SQL> exec validator('AUS',1,sysdate+1000);
BEGIN validator('AUS',1,sysdate+1000); END;
*
ERROR at line 1:
ORA-20000: Invalid data
ORA-06512: at "MCDONAC.VALIDATOR", line 13
ORA-06512: at line 1
SQL>
SQL>