drop package carrier;
/
create or replace package carrier is
/*   procedure AddVisitor(host in char,address in char,user in char,name char);
  procedure showvisitors;
  procedure clearvisitors;
  procedure clear_action(userid in char,passwd in char,clear in number);*/



--procedure bom_admin(userid in varchar2,passwd in varchar2);
procedure bom_admin_home(uid varchar2);
 procedure bom_admin_index;
procedure bom_admin(uid in varchar2,passwd in varchar2);
function validation(uid in varchar2,passwd in varchar2,grp integer) return boolean;
function validation(uid in varchar2,grp integer) return boolean;
 procedure insert_user_index(userid in varchar2);
procedure insert_user(userid in varchar2);
procedure insert_u(uid varchar2,email varchar2, passwd varchar2,name varchar2,grp integer,host varchar2,url varchar2,me varchar2);
procedure all_user(uid in varchar2);
procedure password_typein(url varchar2);
procedure passwd_action(me varchar2,passwd varchar2,uid varchar2);
procedure passwd(me varchar2,uid varchar2);
procedure passwd_action1(old varchar2,p1 varchar2,p2 varchar2,uid varchar2);
procedure passwd1(uid varchar2);
procedure demo_user;
procedure delete_user(me varchar2) ;
procedure delete_user_action(me varchar2,uid varchar2);
procedure insert_bom(me varchar2);
procedure insert_bom_action(me varchar2,p_no in  varchar2,pca  in  varchar2,opca  in  varchar2,type  in  varchar2,family  in  varchar2,
	pitem  in  char,cost in  number,rlevel  in  char,mm  in  char,dd  in  char,yy  in  char,d  in   varchar2) ;
procedure all_bom(uid in varchar2,op integer);
procedure move_pending(uid in varchar2,p varchar2);
procedure move_pending(uid in varchar2);

procedure change(uid in varchar2,p varchar2,mm  in  char,dd  in  char,yy  in  char);
procedure move(uid in varchar2,p varchar2);
procedure notyet(uid varchar2);
procedure notyet;

procedure move_prod(uid varchar2) ;
procedure bom_search_index;
procedure bom_search;
procedure bom_search(op integer,fid varchar2);
procedure search(typein varchar2,op integer,fid varchar2);
procedure all_bom_prod;
procedure bom_list(p varchar2,pca varchar2,time varchar2,f varchar2,c number,t varchar2,i varchar2,r varchar2,
	d varchar2,ctime date);
procedure by_pca(fid varchar2);

procedure search_button(op integer,fid varchar2);
procedure by_pca(fid varchar2,p varchar2) ;
procedure keyword(p varchar2,fid varchar2);
procedure day(str varchar2,result out date,tag in out char) ;
procedure date_search;
procedure parse_date(typein varchar2) ;

function tr(name varchar2) return varchar2;
function th(name varchar2) return varchar2;
function td(name varchar2) return varchar2;

  cgi_bin constant varchar2(60) := '/cgi-bin/demo/wow/';
  username constant varchar2(10) := 'carrier';
  password constant varchar2(10) := 'demo';
  client varchar2(30) := owa.remote_addr;
time_out number(8,5) := 5./1440.;

 help_page constant varchar2(100) := htf.url('http://naos.npac.syr.edu:1963/demo/help.html','Help');

end;
/

create or replace package body carrier is
   total_visitors number(7);
   total number(8);
hh_time date;
h_host users.host%TYPE;
h_grp users.group_id%TYPE;
one users%ROWTYPE;
two bom%ROWTYPE;
h_time varchar2(50) := '<br>';
h_family bom.p_family%TYPE := '<br>';
h_cost bom.s_cost%TYPE;
h_desc varchar2(2000);
h_status varchar2(30):= 'Production (most recent)';
h_caption varchar2(600);

-- for date
TYPE wordstype IS TABLE	OF varchar2(50) INDEX BY BINARY_INTEGER;
word_tab wordstype;
w_count BINARY_INTEGER;
date_tag char(1);

function td(name varchar2) return varchar2 is
begin
	return ('<td>'||name||'</td>');
end;

function th(name varchar2) return varchar2 is
begin
	return ('<th>'||name||'</th>');
end;

function tr(name varchar2) return varchar2 is
begin
	return ('<tr>'||name||'</tr>');
end;

procedure bom_admin_home(uid varchar2) is


begin
	select group_id into h_grp from users where userid=uid;
	htp.p('<center><table border=2 cellspacing=2 cellpadding=4><caption>BoM Document and User Management and Control<caption>');
	htp.p('<tr><th>Operation</th><th>Description</th><th>Authorized User</th></tr>');

	if (h_grp = 5) then
		htp.p(tr(td(htf.url(cgi_bin||'carrier.insert_user_index?userid='||uid,
			'Insert a BoM user'))||td('Insert a new BoM user into the system')||
			td(htf.url('/carrier/users.html','BoM DB Administrator'))));

		htp.p(tr(td(htf.url(cgi_bin||'carrier.delete_user?me='||uid,
			'Delete a BoM user'))||td('Remove an existingBoM user from the system')||
			td(htf.url('/carrier/users.html','BoM DB Administrator'))));

		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.all_user?uid='||uid,
			'List Users/Change Passwords')));
		  htp.p(td('Show all BoM users or change their passwords in the system')||
			td(htf.url('/carrier/users.html','BoM DB Administrator'))||'</tr>');
	end if;


	if (h_grp >= 1) then
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.all_bom?uid='||uid||'&op=1',
			'List All BoM document in production')));
		  htp.p(td('Display all BoM production document, including most recent and all the previous revisions')||
			td(htf.url('carrier/users.html','Produciton BoM User II and III'))||'</tr>');
	end if;
	if (h_grp <= 2 or h_grp >= 5 ) then
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.move_prod?uid='||uid,
			'Move Production BoM''s between Divisions')));
		  htp.p(td('Move BoM ducument owned by one division to another')||
			td(htf.url('carrier/users.html','Produciton BoM User III'))||'</tr>');
	end if;

	if (h_grp >= 3) then
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.all_bom?uid='||uid||'&op=0',
			'List all pending BoM document')));
		  htp.p(td('Display all pending BoM''s and check their effective dates')||
			td(htf.url('carrier/users.html','Pending BoM User II and III'))||'</tr>');
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.insert_bom?me='||uid,
			'Insert a BoM document')));
		  htp.p(td('Insert a new BoM document into the pending document database')||
			td(htf.url('/carrier/users.html','Pending BoM User II and III'))||'</tr>');
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.notyet?uid='||uid,
			'Delete a pending BoM document')));
		  htp.p(td('Delete an existing BoM document from the pending database')||
			td(htf.url('carrier/users.html','Pending BoM User II and III'))||'</tr>');
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.notyet?uid='||uid,
			'Move a pending BoM document to production status')));
		  htp.p(td('change a pending BoM status to production after the effective date is reached')||
			td(htf.url('carrier/users.html','Pending BoM User II and III'))||'</tr>');
	end if;

	if (h_grp >= 4) then
		htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.assign_pending?uid='||uid,
			'change/assign an effective date to a pending BoM document')));
		  htp.p(td('assign or change the effective date for a pending BoM to be moved to production')||
			td(htf.url('carrier/users.html','Pending BoM User III'))||'</tr>');
	end if;

	htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.passwd1?uid='||uid,
		'Change your password'))||td('Create a new password for yourself'));
		htp.p(td(htf.url('carrier/users.html','Any registered BoM user'))||'</tr>');


	htp.p('</table></center>');
	wow.sig;
end;

 procedure bom_admin_index is
begin
	htp.p('<body bgcolor="#ffffff">');
	htp.p('This page is for authorized user only.<hr width="80%">');
	htp.p('<form action="'||cgi_bin||'carrier.bom_admin" method="POST">');
	htp.p('<center><table>');
	htp.p('<tr><td align=right>User Id :</td><td><input name="uid" value="" size="20"></td></tr>');
	htp.p('<tr><td align=right>Password :</td><td><input type="password" name="passwd" value="" size="20"></td></tr>');
	htp.p('</table>');
	htp.p(htf.formDo('Submit')); 
	htp.p('</form></center>');  
	demo_user;    

end;
procedure demo_user is
begin
	htp.p('<center><table border=1><caption>Demo Users</caption>');
	htp.p(tr(th('Userid/Password')||th('Privilege')));
	  htp.p(tr(td(' root/demo')||td('   BoM DB Admin.')));
	  htp.p(tr(td(' user2/user2')||td('   BoM Production User II')));
  	  htp.p(tr(td(' user3/user3')||td('   BoM Production User III')));
	 htp.p(tr(td(' user4/user4  ')||td(' BoM Pending User II')));
  	  htp.p(tr(td(' user5/user5')||td('   BoM Pending User III')));
		htp.p('</table></center>');

end;

procedure bom_admin(uid in varchar2,passwd in varchar2) is

begin
	htp.p('<body bgcolor="#ffffff">');
	
	if (validation(uid,passwd,1)) then
		bom_admin_home(uid);
	else
		bom_admin_index;
	end if;
	
end;


procedure password_typein(url varchar2) is

begin
	  htp.p('This operation is for authorized BoM DB admin. only.<hr width="80%">');
	  htp.p('<form action="'||url||'" method="POST">');
	  htp.p('<center><table>');
	  htp.p('<tr><td align=right>User Id :</td><td><input name="userid" value="" size="20"></td></tr>');
	  htp.p('<tr><td align=right>Password :</td><td><input type="password" name="passwd" value="" size="20"></td></tr>');
	  htp.p('</table>');
	  htp.p(htf.formDo('Submit')); 
	  htp.p('</form></center>');  
	    demo_user;    
end;


 procedure insert_user_index(userid in varchar2) is
begin
	--htp.p('<body bgcolor="#ffffff">');
	if (validation(userid,5)) then
	  insert_user(userid);
	end if;
end;

function validation(uid in varchar2,passwd in varchar2,grp integer) return boolean is
cursor cuser(uid varchar2,pwd varchar2) is select group_id from users 
	where userid=uid and password=pwd;
h_grp integer;
begin
	open cuser(uid,passwd);
	fetch cuser into h_grp;

	if (cuser%NOTFOUND) then
	   close cuser;
	   htp.p('<body bgcolor="#ffffff">');
	   htp.p('<center>You have the wrong <blink>userid/password</blink> !</center>');
	   htp.p('Try again.');
	   return false;
	elsif ((h_grp<grp) or ((h_grp >=4) and (h_host!=client))) then
	   close cuser;
	   htp.p('<body bgcolor="#ffffff">');
	   htp.p('<center>You <blink>do not have the privilege</blink>to perform this operation.</center>');
	   return false;
	end if;
	close cuser;
	update users set time=sysdate where userid=uid;
	update users set host=client where userid=uid and group_id >=4;
	commit;
	return true;

end;

function validation(uid in varchar2,grp integer) return boolean is
cursor cuser(u varchar2,g integer) is select time,host from users 
	where userid=u and group_id>=g;
begin
	open cuser(uid,grp);
	fetch cuser into hh_time,h_host;

	if ((cuser%NOTFOUND) or (h_host != client)) then
	   close cuser;
	   htp.p('<body bgcolor="#ffffff">');
	   htp.p('<center>You <blink>do not have the privilege</blink>to perform this operation.<p>');
	   htp.p('Go back.</center>');
	   return false;
	elsif ((sysdate - hh_time) > time_out) then  -- 10 minutes
	   close cuser;
	   htp.p('<body bgcolor="#ffffff">');
	   htp.p('Your login time is out. Last login: '||to_char(hh_time,'mm/dd/yy hh24:mi')||' EST.');
	   htp.p('Re-login again. <hr>');
	   bom_admin_index;
	   return false;
	end if;
	close cuser;
	update users set time=sysdate where userid=uid;
	commit;
	return true;

end;


procedure insert_user(userid in varchar2) is

begin
	htp.p('<body bgcolor="#ffffff">');
	htp.p('<center><h2>Insert a new BoM user</h2></center>');	
	htp.p('<form action="'||cgi_bin||'carrier.insert_u" method="POST">');
	htp.p('<center><table>');
	htp.p('<tr><td align=right>User Id :</td><td><input name="uid" value="" size="20"></td></tr>');
	htp.p('<tr><td align=right>Initial Password :</td><td><input type="password" name="passwd" value="" size="20"></td></tr>');
	htp.p('<tr><td align=right>Name :</td><td><input name="name" value="" size="20"></td></tr>');
	htp.p('<tr><td align=right>Email Address :</td><td><input name="email" value="" size="20"></td></tr>');
	htp.p('<tr><td align=right>Privilege :</td><td><select name="grp"><option value="1">Production User II');
	htp.p('<option value="2">Production User III');
	htp.p('<option value="3">Pending User II');
	htp.p('<option value="4">Pending User III</select></td></tr>');
	htp.p('<tr><td align=right>Client Host (for User III only) :</td><td><input name="host" value="" size="20"></td></tr>');
	htp.p('<tr><td align=right>URL (optional):</td><td><input name="url" value="N/A" size="20"></td></tr>');
	htp.p('<input type="hidden" name="me" value="'||userid||'">');
	htp.p('</table>');
	htp.p(htf.formDo('Submit')); htp.p('<input type="reset" value="Reset">');
	htp.p('</form></center><hr>');  
	all_user(userid);
	wow.sig;
	
end;

procedure all_user(uid varchar2) is
cursor cuser is select * from users order by userid;
h_time varchar2(20) := '<br>';
h_host users.host%TYPE := '<br>';
h_url users.url%TYPE;
h_grp varchar2(20) := 'Production User II';

begin
	if (not validation(uid,5)) then
		return;
	end if;
	htp.p('<body bgcolor="#ffffff">');
	htp.p('<hr>');
	select count(*) into total from users;
	if (total > 0 ) then  
		htp.p('There are total '||htf.bold(total)||' users.');
		htp.p('<center><table border=1>');
		htp.p('<caption>Current BoM users in the system</caption>');

		htp.p(tr(th('User Id')||th('Name')||th('User Group')||th('Hostname')||th('URL')||th('Last login')));
		open cuser;
		loop
			fetch cuser into one;
			exit when cuser%NOTFOUND;
			if (one.group_id = 2) then
				h_grp := 'Production User III';
			elsif (one.group_id = 3) then
				h_grp := 'Pending User II';
			elsif (one.group_id = 4) then
				h_grp := 'Pending User III';
			elsif (one.group_id = 5) then
				h_grp := 'BoM DB Administrator';			
			end if;
			if (one.url IS NULL or one.url like '%N/A%') then
				h_url := '<br>';
			else
				h_url := htf.url(one.url,one.url);
			end if;
			if (one.time IS NOT NULL) then
				h_time := to_char(one.time,'mm/dd/yy, hh24:mi')||' EST';
			else
				h_time := '<br>';
			end if;
			if (one.group_id >= 3 and one.host IS NOT NULL) then
				h_host := one.host;
			else
				h_host := '<br>';
			end if;
			htp.p(tr(td(htf.url(cgi_bin||'carrier.passwd?me='||uid||'&uid='||one.userid,one.userid))||
			td(one.name)||td(h_grp)||td(h_host)||td(h_url)||td(h_time)));
		end loop;		
		close cuser;
		htp.p('</table></center>');
	else
		htp.p('No user for BoM system yet.');
	end if;
end;

procedure passwd(me varchar2,uid varchar2) is

begin
	if (validation(me,5)) then
		htp.p('<body bgcolor="#ffffff"><hr>');
		select * into one from users where userid = uid;
		htp.p('<form action="'||cgi_bin||'carrier.passwd_action" method=POST');
		htp.p('<center><table border=1>');
	  	htp.p('<caption>Change a user''s password</caption>');
		htp.p(tr(td('User Id: ')||td(one.userid)));
		htp.p(tr(td('Name: ')||td(one.name)));
		htp.p(tr(td('New Password: ')||td('<input type="password" name="passwd" value="">')));
		htp.p('</table>');
		htp.p('<input type="hidden" name="uid" value="'||uid||'">');
		htp.p('<input type="hidden" name="me" value="'||me||'">');
		htp.p(htf.formDo('Submit')); 
		htp.p('</center></form>');
		wow.sig;
	end if;
end;

procedure passwd1(uid varchar2) is

begin
	if (validation(uid,1)) then
		htp.p('<body bgcolor="#ffffff"><hr>');
		select * into one from users where userid = uid;
		htp.p('<form action="'||cgi_bin||'carrier.passwd_action1" method=POST');
		htp.p('<center><table border=1>');
	  	htp.p('<caption>Change your password</caption>');
		htp.p(tr(td('User Id: ')||td(one.userid)));
		htp.p(tr(td('Name: ')||td(one.name)));
		htp.p(tr(td('Old Password: ')||td('<input type="password" name="old" value="">')));
		htp.p(tr(td('New Password: ')||td('<input type="password" name="p1" value="">')));
		htp.p(tr(td('New Password Again: ')||td('<input type="password" name="p2" value="">')));
		htp.p('</table>');
		htp.p('<input type="hidden" name="uid" value="'||uid||'">');
		--htp.p('<input type="hidden" name="me" value="'||me||'">');
		htp.p(htf.formDo('Submit')); 
		htp.p('</center></form>');
		wow.sig;
	end if;
end;


procedure passwd_action1(old varchar2,p1 varchar2,p2 varchar2,uid varchar2) is

begin
	if (validation(uid,1)) then
		select * into one from users where userid = uid;
		if (one.password != old or (p1!=p2)) then
			htp.p('Wrong password. Try again.<hr>');
			passwd1(uid);
			return;
		end if;
		htp.p('<body bgcolor="#ffffff"><hr>');
		update users set password=p1 where userid=uid;
		commit;
		htp.p('Your password has been changed.<hr>');
		bom_admin_home(uid);
	end if;
end;


procedure passwd_action(me varchar2,passwd varchar2,uid varchar2) is

begin
	if (validation(me,5)) then
		htp.p('<body bgcolor="#ffffff"><hr>');
		update users set password=passwd where userid=uid;
		commit;
		htp.p(htf.bold(uid)||' password has been changed.<hr>');
		bom_admin_home(uid);
	end if;
end;

procedure insert_u(uid varchar2,email varchar2, passwd varchar2,name varchar2,grp integer,host varchar2,url varchar2,me varchar2) is
cursor cuser(u varchar2) is select * from users where userid=u;
uu users%ROWTYPE;
begin

	if ((uid||passwd||name) IS NULL) then
		htp.p('The field '||htf.bold('User Id/Name/Password')||' must not be blank.');
		htp.p('<p>Go back and correct it. Try again.');
		return;
	end if;
	if (grp >= 4 and host IS NULL) then
		htp.p('The field '||htf.bold('Host')||' must not be blank.');
		htp.p('<p>Go back and correct it. Try again.');
		return;
	end if;

	open cuser(uid);
	fetch cuser into uu;
	if (cuser%FOUND) then
		htp.p('There is already a user with the userid = '||htf.bold(uid)||' in the system.');
		htp.p('<p>Go back and correct it. Try again.');
		return;
	else
		insert into users values(uid,passwd,email,name,grp,host,sysdate,url,null,null);
		COMMIT;
	end if;
	htp.p('<hr><p>The new user '||htf.bold(uid)||' ('||name||') has been sucessfully added into the BoM user list.');
	htp.p('<p>You may send an email to inform <a href="mailto:'||email||'">'||email||'</a> of this new account.');
	all_user(me);	
	wow.sig;
end;

procedure delete_user(me varchar2) is
cursor cuser is select * from users where group_id <= 5 order by userid;
begin

	if (validation(me,5)) then
		htp.p('<body bgcolor="#ffffff"><hr>');
		select count(*) into total from users;
		htp.p('<center>Total '||total||' BoM users. Which one to remove ?:<br>');
		open cuser;
		htp.p('<form action="'||cgi_bin||'carrier.delete_user_action">');
		htp.p('<select name=uid>');
		loop
			fetch cuser into one;
			exit when cuser%NOTFOUND;
			htp.p('<option value="'||one.userid||'">'||one.userid||'('||one.name||')');
		end loop;
		htp.p('</select>');
		close cuser;		
		htp.p(htf.formDo('Delete')); 
		htp.p('<input type="hidden" name="me" value="'||me||'">');
		htp.p('</center></form>');
		htp.p('<form action="'||cgi_bin||'carrier.delete_user_action">');
		htp.p('<input type="hidden" name="me" value="'||me||'">');

	end if;
end;

procedure delete_user_action(me varchar2,uid varchar2) is

begin

	if (validation(me,5)) then
		htp.p('<body bgcolor="#ffffff"><hr>');
		delete from users where userid=uid;
		commit;
		htp.p('User '||htf.bold(uid)||' has been removed.<hr>');
		all_user(me);
		wow.sig;
	end if;
end;

procedure insert_bom(me varchar2) is
cursor cbom is select distinct pca from bom order by pca;
h_pca bom.pca%TYPE;
begin

	if (validation(me,3)) then
		htp.p('<body bgcolor="#ffffff"><hr>');

htp.bp('<font size="-1">
This page allows a user to insert a new BoM document with required
attributes and indexes into the database so that other users can search
and access. Upon the entering of a new document into the DB, the infomation
will be validated to make sure data consisency and other criteria required
by a relational database management system. If invalid data entry is found, the
user will be requested to re-enter the entry. If all data are correct,
the new information will be inserted and are immediately available to other
users to share.

The data items used here are fake data for demo only. In actual situation,
more complex data structure and data entry requirement are expected.
</font>

<hr height="5" width="90%">
<center>');

htp.p('<form action="'||cgi_bin||'carrier.insert_bom_action" method="POST">');
htp.bp('<table border=2 align="center">
<caption>Insert a new BoM document</caption>
<tr><th>Data Item</th><th>New Value to be inserted</th>
<tr><td>Part No.<td><input size="30" name="p_no">
<tr><td>New PCA<td><input size="15" name="pca">');
	open cbom;
	loop
		fetch cbom into h_pca;
		exit when cbom%NOTFOUND;
		if cbom%ROWCOUNT = 1 then
			htp.p('Or choose from: <select name="opca">');
		end if;
		htp.p('<option value="'||h_pca||'">'||h_pca);
	end loop;
	if (cbom%ROWCOUNT = 0) then 
		htp.p('<input type="hidden" name="opca" value="">');
	else
		htp.p('</select>');
	end if;
	close cbom;

htp.bp('<tr><td>Item Status<td>Pending
<tr><td>BoM Item Type<td><select name="type">
	<option value="Standard">Standard<option value="Customized">Customized
	<option value="Special">Special<option value="Priority">Priority</select>

<tr><td>Product Family<td><input size="30" name="family">
<tr><td>Purchased Item<td><select name="pitem">
	<option value="y">Yes
	<option value="n">No
	</select>
<tr><td>STD Unit Cost<td><input size="30" name="cost">
<tr><td>Revise Level<td><select name="rlevel">
	<option value="a">A<option value="b">B
	<option value="c">C<option value="d">D
	<option value="e">E<option value="f">F
	<option value="g">G<option value="h">H</select>
<tr><td>Effective Date<td><input name="mm" size=2>/<input name="dd" size=2>/
<input size=2 name="yy" value="96"> (mm/dd/yy)
<tr><td>Description<td><textarea rows=5 cols=30 name="d"></textarea>
</table>
<input type="submit" value="Insert">
<input type="reset" value="Reset">');
htp.p('<input type="hidden" name="me" value="'||me||'">');

htp.p('</form></center>');

	end if;

	wow.sig;
end;


procedure insert_bom_action(me varchar2,p_no in  varchar2,pca  in  varchar2,opca  in  varchar2,type  in  varchar2,family  in  varchar2,
	pitem  in  char,cost in  number,rlevel  in  char,mm  in  char,dd  in  char,yy  in  char,d  in   varchar2) is
edate date;
cursor cbom(p varchar2) is select part_no from bom where part_no = p;
h_part_no bom.part_no%TYPE;
h_pca bom.pca%TYPE;
begin

	if (not validation(me,3)) then
		return;
	end if;
	htp.p('<body bgcolor="#ffffff"><hr>');
	h_pca:=pca;
	if (pca IS NULL) then
		h_pca := opca;
	end if;
	if (p_no IS NULL or h_pca IS NULL) then
		htp.p('The Part No. and PCA must be provided. Try again.<hr>');
		insert_bom(me); return;
	end if;

	if ((mm||dd) IS NOT NULL) then
		edate := to_date(lpad(mm,2,'0')||lpad(dd,2,'0')||lpad(yy,2,'0'),'mmddyy');
		if (to_char(edate,'mmddyy') < to_char(sysdate,'mmddyy')) then
			htp.p('**Invalid Date**: The effective date you enterered ('||edate||') is less than');
			htp.p('today ('||sysdate||'). Try again.<hr>');
			insert_bom(me); return;
		end if;
	end if;
		
	open cbom(p_no);
	fetch cbom into h_part_no;
	if (cbom%FOUND) then
		close cbom;
		htp.p('There is already a BoM document with the part no. = '||htf.bold(p_no)||' in the system.');
		htp.p('<p>Correct it. Try again.');insert_bom(me); 
		return;
	else
		insert into bom values(p_no,rlevel,pitem,h_pca,'Pending',sysdate,family,cost,edate,d,type);
		COMMIT;
	end if;
	close cbom;
	htp.p('<p>The BoM document (Part No.='||htf.bold(p_no)||') has been sucessfully added into the BoM pending document DB.');
	all_bom(me,0);	
	wow.sig;
end;

procedure move_prod(uid varchar2) is
begin
	if (not validation(uid,2)) then
		return;
	end if;
	notyet(uid);
end;

procedure notyet(uid varchar2) is
begin
	if (not validation(uid,1)) then
		return;
	end if;
	htp.p('<center>Sorry, this function has not implemented yet in the demo. Stay tuned!');
	bom_admin_home(uid);
end;

procedure move_pending(uid in varchar2) is

begin
	if (not validation(uid,3)) then
		return;
	end if;
	htp.p('Choose a document for the list below');
	 all_bom(uid,0);
end;

procedure move_pending(uid in varchar2,p varchar2) is
h_date date;
h_grp integer;
h_status bom.status%TYPE;
begin

	if (not validation(uid,3)) then
		return;
	end if;
	
	htp.p('<body bgcolor="#ffffff">');
	htp.p('<hr>');
	select group_id into h_grp from users where userid=uid;
	select effective_date,status into h_date,h_status from bom where part_no=p;
	if (h_grp >=3 and lower(h_status) like '%pending%') then
		if (to_char(h_date,'mmddyy') <= to_char(sysdate,'mmddyy')) then
			htp.p(htf.url(cgi_bin||'carrier.move?uid='||uid||'&p='||p,'Move this document to production'));
			htp.p('<hr>');
		else
			htp.p('This document ('||htf.bold(p)||') has not reached its effective day yet. Effective='||h_date);
			htp.p('.<br>You can not move it to the production area. Wait until its effective date.');
		end if;
	end if;
	if (h_grp >=4 ) then
		htp.p('<form action="'||cgi_bin||'carrier.change">');
		htp.p('Change the effective date from '||htf.bold(h_date));
		htp.p('<p> to <input name="mm" size=2>/<input name="dd" size=2>/<input size=2 name="yy" value="96"> (mm/dd/yy)');
		htp.p('<input type="submit" value="Change">');
		htp.p('<input type="hidden" name="uid" value="'||uid||'">');
		htp.p('<input type="hidden" name="p" value="'||p||'">');
		htp.p('</form>');
	end if;
		
end ;

procedure change(uid in varchar2,p varchar2,mm  in  char,dd  in  char,yy  in  char) is
edate date;
begin
	if (not validation(uid,3)) then
		return;
	end if;
	if ((mm||dd) IS NOT NULL) then
		edate := to_date(lpad(mm,2,'0')||lpad(dd,2,'0')||lpad(yy,2,'0'),'mmddyy');
		if (to_char(edate,'mmddyy')< to_char(sysdate,'mmddyy')) then
			htp.p('**Invalid Date**: The effective date you enterered ('||edate||') is less than');
			htp.p('today ('||sysdate||'). Try again.<hr>');
			move_pending(uid,p); return;
		end if;
	else
		htp.p('Wrong Date. Try again.');
		move_pending(uid,p); return;
	end if;
	htp.p('<body bgcolor="#ffffff">');
	htp.p('<hr>');
	update bom set effective_date=edate where part_no=p;
	commit;
	htp.p('The effective date of the pending document ('||htf.bold(p)||') has been assigned/changed to '||edate);
	all_bom(uid,0);
end;


procedure move(uid in varchar2,p varchar2) is
begin
	if (not validation(uid,3)) then
		return;
	end if;
	
	htp.p('<body bgcolor="#ffffff">');
	htp.p('<hr>');
	update bom set status='production' where part_no=p;
	commit;
	htp.p('The pending document ('||htf.bold(p)||') has been sucessfully moved to the produciton area.');
	all_bom(uid,0);
end;


procedure all_bom(uid in varchar2,op integer) is
cursor cuser(s varchar2) is select * from bom where lower(status) like '%'||s||'%' order by part_no;
h_time varchar2(50) := '<br>';
h_family bom.p_family%TYPE := '<br>';
h_cost bom.s_cost%TYPE;
h_desc varchar2(2000);
h_status bom.status%TYPE := 'pending';
begin
	if (not validation(uid,3)) then
		return;
	end if;
	if (op = 1) then h_status := 'production'; end if;
	htp.p('<body bgcolor="#ffffff">');
	htp.p('<hr>');
	select count(*) into total from bom where lower(status) like '%'||h_status||'%';
	if (total > 0 ) then  
		htp.p('There are total '||htf.bold(total)||' BoM '||h_status||' documents.<hr width=50%>');
		htp.p('<center><table border=1 align=center>');
		
		htp.p('<caption>Current BoM '||h_status||' documents in the system</caption>');

		htp.p(tr(th('Part No.')||th('PCA')||th('Effective Date(EST)')||th('Part Family')||th('Unit Cost')||
		th('Item Type')||th('Purchased Item')||th('Revise Level')||th('Description')||th('Creation Date(EST)')));
		open cuser(h_status);
		loop
			fetch cuser into two;
			exit when cuser%NOTFOUND;

			if (two.effective_date IS NOT NULL) then
				if (to_char(two.effective_date,'mmddyy')<=to_char(sysdate,'mmddyy') and op=0) then
					h_time := '<blink>'||to_char(two.effective_date,'mm/dd/yy, hh24:mi')||'</blink>';
				else
					h_time := to_char(two.effective_date,'mm/dd/yy, hh24:mi');
				end if;
			else
				h_time := 'not set yet';
			end if;
			if (two.p_family IS NULL) then
				h_family  := '<br>';
			else
				h_family  := two.p_family;
			end if;
			if (two.s_cost IS NULL) then
				h_cost  := 0.;
			else
				h_cost := two.s_cost;
			end if;
			if (two.description IS NULL) then
				h_desc := '<br>';
			else
				h_desc := two.description;
			end if;
			if (op=0) then
				htp.p('<tr>'||td(htf.url(cgi_bin||'carrier.move_pending?uid='||uid||'&p='||two.part_no,two.part_no)));
			else
				htp.p('<tr>'||td(two.part_no));
			end if;
			htp.p(td(two.pca)||td(h_time)||td(h_family)||td(h_cost));
			htp.p(td(two.b_type)||td(two.p_item)||td(two.r_level)||td(h_desc)||
			td(to_char(two.revise_date,'mm/dd/yy, hh24:mi'))||'</tr>');
		end loop;		
		close cuser;
		htp.p('</table>');
		if (op=0) then
			htp.p('<font size=-1>If you noticed any document with blink effective date, that document needs to be
moved immediately to the production area.</font>');
		end if;
		htp.p('</center>');
	else
		if (op=0) then
			htp.p('No BoM Pending document yet.');
		else
			htp.p('No BoM Production document yet.');
		end if;
	end if;
wow.sig;
end;


procedure bom_search_index is
begin
	htp.p('<center><h2>Carrier RTS BoM Document Search Page</h2></center>');

htp.p('<body bgcolor="#ffffff">');
	htp.p('<ul><li>'||htf.url(cgi_bin||'carrier.bom_search','Retrieval of BoM Production Documents'));
    htp.bp('<ul><li>locate/search BoM production document and associated attributes with
indexes, including most recent and previous revisions.
    <li>list all BoM production document in the DB.
    <li>search certain BoM production document which satisfy one or more
user-entered conditions/attributes, such as search by a PCA, by a part
number, effective date, revision date, etc.
        </ul>');

htp.bp('<p><li>'||htf.url(cgi_bin||'carrier.bom_admin_index','Retrieval of BoM Pending Documents')||'(restricted access)');
        htp.bp('<ul>
        <li>locate/search BoM pending document and associated attributes with indexes.
        <li>list all BoM pending document in the DB.
        <li>search certain BoM pending document which satisfy one or more
user-entered conditions/attributes, such as search by a PCA, by a part
number, effective date, etc.
	<li>Other administration tasks, including load/delete/modify/move a pending document.</ul>');
	wow.sig;
end;

procedure bom_search is
begin 
	 bom_search(6,0);
end;

procedure search_button(op integer,fid varchar2) is
cursor cfolder is select pca from bom where lower(status) like '%prod%' order by pca;
begin
htp.p('<body bgcolor="#ffffff">');

	htp.p('<CENTER>');
	htp.p('<table border=0 align=center><tr><td colspan="2">');
	htp.formOpen(cgi_bin||'carrier.search');	
	htp.p(htf.url_top('http://www.npac.syr.edu/','<img src="/demo/images/npaclogo.gif" border=0 align="middle">'));
	htp.p('<input name="typein" size="40" value=""> ');
	htp.p('<input type="submit" value="Search"> ');
	htp.p(help_page);
	htp.p('</td></tr><tr align=center><td><font size="2">By: ');
	htp.formSelectOpen('op');
	if (op = 0) then
		htp.p('<option value="0" selected>Keyword(in description)');	
	else
		htp.p('<option value="0">Keyword(in description)');
	end if;	
	if (op = 1) then
		htp.p('<option value="1" selected>Part No. (patial or full)');
	else
		htp.p('<option value="1">Part No.');
	end if;
	if ( op = 6) then
		htp.p('<option value="6" selected>List All');	
	else
		htp.p('<option value="6">List All');	
	end if;
	if ( op = 2) then
		htp.p('<option value="2" selected>Effective Date');
	else
		htp.p('<option value="2">Effective Date');
	end if;
	if ( op = 3) then
		htp.p('<option value="3" selected>Purchased Item(Yes)');	
	else
		htp.p('<option value="3">Purchased Item(Yes)');	
	end if;
	if ( op = 4) then
		htp.p('<option value="4" selected>Purchased Item(No)');	
	else
		htp.p('<option value="4">Purchased Item(No)');	
	end if;
	if ( op = 5) then
		htp.p('<option value="5" selected>Revise Level');	
	else
		htp.p('<option value="5">Revise Level');	
	end if;


	htp.p(htf.formSelectClose);	
	htp.p('</font></td>');
	htp.p('<td><font size="2">PCA:');
	select count(*) into total from bom where lower(status) like '%production%';
	if (total > 20) then
		htp.p('<select name="fid" size="2">');
	else
		htp.p('<select name="fid">');
	end if;
	if (fid = '0') then
		htp.p('<option value="0" selected>All ('||total||')');
	else
		htp.p('<option value="0">All ('||total||')');
	end if;
	for bom in cfolder loop
		if (fid = bom.pca) then
	   		htp.p('<option value="'||bom.pca||'" selected>'||bom.pca);
		else
	   		htp.p('<option value="'||bom.pca||'">'||bom.pca);
		end if;
	end loop;
	htp.p(htf.formSelectClose);
	htp.p('</font></td></tr>');
	htp.p(htf.formClose);
	htp.p('</table></CENTER>');

end;

procedure bom_search(op integer,fid varchar2) is
	begin
		search_button(op,fid);
		all_bom_prod;
	end;

procedure search(typein varchar2,op integer,fid varchar2) is
begin
	if ((op = 6 or op=1) and fid='0') then
		bom_search;
		return;
	end if;
	if (op=6) then
		by_pca(fid,0);
		return;
	end if;
	if (op=1) then
		by_pca(fid,lower(typein));
		return;
	end if;

	if (op=0) then
		keyword(lower(typein),fid);
		return;
	end if;
	if (op=2) then
		by_date(typein);
		return;
	end if;
	notyet;

	wow.sig;
end;

procedure keyword(p varchar2,fid varchar2) is
cursor cuser(p varchar2) is select * from bom where (lower(status) like '%prod%')
	and (description like  '%'||p||'%') order by part_no asc,pca asc,r_level desc;

cursor cuser1(fid varchar2,p varchar2) is select * from bom where (lower(status) like '%prod%')
	and (pca=fid) and (lower(description) like '%'||p||'%') order by part_no asc,pca asc,r_level desc;
begin
	search_button(0,fid);
	htp.p('<hr width=80%>');
	htp.p('<center><table border=1 align=center>');

	if fid = '0' then
		open cuser(p);
		h_caption := 'for all PCAs containing keyword '||htf.bold(p);
	else
		open cuser1(fid,p);
		h_caption := 'with PCA='||htf.bold(fid)||' containing keyword '||htf.bold(p);
	end if;
	htp.p('<caption>Current All BoM Production Documents');
	htp.p(h_caption||'</caption>');
	htp.p(tr(th('Part No.')||th('PCA')||th('Effective Date(EST)')||th('Part Family')||th('Unit Cost')||
	th('Item Type')||th('Purchased Item')||th('Revise Level')||th('Description')||th('Creation Date(EST)')));
	loop
		if fid = '0' then
			fetch cuser into two;
			exit when cuser%NOTFOUND;
		else
			fetch cuser1 into two;
			exit when cuser1%NOTFOUND;
		end if;
		h_time := to_char(two.effective_date,'mm/dd/yy, hh24:mi');
			if (two.p_family IS NULL) then
			h_family  := '<br>';
		else
			h_family  := two.p_family;
		end if;
		if (two.s_cost IS NULL) then
			h_cost  := 0.;
		else
			h_cost := two.s_cost;
		end if;
		if (two.description IS NULL) then
			h_desc := '<br>';
		else
				h_desc := two.description;
				h_desc := replace(h_desc,p,'<blink>'||p||'</blink>');
		end if;

		bom_list(two.part_no,
			two.pca,h_time,h_family,h_cost,
			two.b_type,two.p_item,two.r_level,h_desc,two.revise_date);
	end loop;		
	if fid='0' then close cuser;
	else close cuser1; end if;

	htp.p('</table>');
	htp.p('</center>');
	wow.sig;
end;

procedure by_pca(fid varchar2) is
begin by_pca(fid,0); end;

procedure by_pca(fid varchar2,p varchar2) is
cursor cuser(fid varchar2) is select * from bom where (lower(status) like '%prod%')
	and (pca=fid) order by part_no asc,pca asc,r_level desc;

cursor cuser1(fid varchar2,p varchar2) is select * from bom where (lower(status) like '%prod%')
	and (pca=fid) and (lower(part_no) like '%'||p||'%') order by part_no asc,pca asc,r_level desc;
begin
	search_button(1,fid);

	if p = '0' then
		open cuser(fid);
		h_caption := 'with PCA='||htf.bold(fid);
	else
		open cuser1(fid,p);
		h_caption := 'with PCA='||htf.bold(fid)||' and Part No.=*'||p||'*';
	end if;

	htp.p('<hr width=80%>');
	htp.p('<center><table border=1 align=center>');
	htp.p('<caption>Current All BoM Production Documents '||h_caption||'</caption>');
	htp.p(tr(th('Part No.')||th('PCA')||th('Effective Date(EST)')||th('Part Family')||th('Unit Cost')||
	th('Item Type')||th('Purchased Item')||th('Revise Level')||th('Description')||th('Creation Date(EST)')));
	loop
		if p = '0' then
			fetch cuser into two;
			exit when cuser%NOTFOUND;
		else
			fetch cuser1 into two;
			exit when cuser1%NOTFOUND;
		end if;
		h_time := to_char(two.effective_date,'mm/dd/yy, hh24:mi');
			if (two.p_family IS NULL) then
			h_family  := '<br>';
		else
			h_family  := two.p_family;
		end if;
		if (two.s_cost IS NULL) then
			h_cost  := 0.;
		else
			h_cost := two.s_cost;
		end if;
		if (two.description IS NULL) then
			h_desc := '<br>';
		else
				h_desc := two.description;
		end if;
		bom_list(two.part_no,
			two.pca,h_time,h_family,h_cost,
			two.b_type,two.p_item,two.r_level,h_desc,two.revise_date);
	end loop;		
	if p='0' then close cuser;
	else close cuser1; end if;

	htp.p('</table>');
	htp.p('</center>');
	wow.sig;
end;

procedure all_bom_prod is
cursor cuser is select * from bom where lower(status) like '%prod%' 
	order by part_no asc, pca asc,r_level desc;
h_time varchar2(50) := '<br>';
h_family bom.p_family%TYPE := '<br>';
h_cost bom.s_cost%TYPE;
h_desc varchar2(2000);
h_status varchar2(30):= 'Production (most recent)';
begin
--	htp.p('<body bgcolor="#ffffff">');
	htp.p('<hr width=80%>');
	select count(*) into total from bom where lower(status) like '%prod%';
	if (total > 0 ) then  
		htp.p('<center><table border=1 align=center>');
		htp.p('<caption>Current All BoM Production Documents in DB</caption>');

		htp.p(tr(th('Part No.')||th('PCA')||th('Effective Date(EST)')||th('Part Family')||th('Unit Cost')||
		th('Item Type')||th('Purchased Item')||th('Revise Level')||th('Description')||th('Creation Date(EST)')));
		open cuser;
		loop
			fetch cuser into two;
			exit when cuser%NOTFOUND;
			h_time := to_char(two.effective_date,'mm/dd/yy, hh24:mi');

			if (two.p_family IS NULL) then
				h_family  := '<br>';
			else
				h_family  := two.p_family;
			end if;
			if (two.s_cost IS NULL) then
				h_cost  := 0.;
			else
				h_cost := two.s_cost;
			end if;
			if (two.description IS NULL) then
				h_desc := '<br>';
			else
				h_desc := two.description;
			end if;
			bom_list(two.part_no,
				two.pca,h_time,h_family,h_cost,
				two.b_type,two.p_item,two.r_level,h_desc,two.revise_date);
		end loop;		
		close cuser;
		htp.p('</table>');
		htp.p('</center>');
	else
		htp.p('No BoM Production document yet.');
	end if;

end;

procedure bom_list(p varchar2,pca varchar2,time varchar2,f varchar2,c number,t varchar2,i varchar2,r varchar2,
	d varchar2,ctime date) is

begin
	htp.p('<tr align=center>'||td(p)||td(pca)||td(time)||
		td(f)||td(c));
	htp.p(td(t)||td(i)||td(r)||td(d)||td(to_char(ctime,'mm/dd/yy, hh24:mi')));
end;

procedure notyet is
begin
	search_button(6,0);
	htp.p('<hr><center>Sorry, this function has not implemented yet in the demo. Stay tuned!');
end;

procedure by_date(typein varchar2) is

begin
	parse_date(typein);
	if (w_count = 0) then
		bom_search;
	else
		search_button(2,0);
		htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
		date_search;
	end if;
end;


procedure parse_date(typein varchar2) is
words varchar2(300);
i integer := 1;
j integer;
n integer;
begin
	w_count := 0;
	words := typein;
	while ((words IS NOT NULL) and (substr(words,1,1) = ' ')) loop
			words := substr(words,2);
	end loop;
	if (words IS NULL) then
		return;
	end if;
	n := length(words);
	-- 47 = '/'; 48-57 = '0'-'9'
	while (i<=n and (ascii(substr(words,i,1)) between 47 and 57)) loop
		i := i + 1;
	end loop;
	if (i = 1) then
		words := substr(words,2); 	
		while ((words IS NOT NULL) and not (ascii(substr(words,1,1)) between 47 and 57)) loop
			words := substr(words,2);
		end loop;
		-- when i>n w_count=0 is used to indicate the null typein
		if (words IS NOT NULL) then
			date_tag := '<'; -- before one date
			n := length(words);
			i := 1;
			while (i<=n and (ascii(substr(words,i,1)) between 47 and 57)) loop
				i := i + 1;
			end loop;
			w_count := w_count + 1;
			word_tab(w_count) := substr(words,1,i-1);
		end if;
	elsif (i > n) then
		date_tag := 'o';	-- one date item only
		w_count := w_count + 1;
		word_tab(w_count) := words;
	else
		w_count := w_count + 1;
		word_tab(w_count) := substr(words,1,i-1);
		words := substr(words,i);
		n := length(words);
		i := 1;
		while (i<=n and not (ascii(substr(words,i,1)) between 47 and 57)) loop
			i := i + 1;
		end loop;		
		if (i > n) then
			date_tag := '>';	-- after one date
		else
			date_tag := 'b';	-- between two date items	
			words := substr(words,i);
			n := length(words);
			i := 1;
			while (i<=n and (ascii(substr(words,i,1)) between 47 and 57)) loop
				i := i + 1;
			end loop;
			w_count := w_count + 1;
			word_tab(w_count) := substr(words,1,i-1);
		end if;
	end if;
end;

procedure date_search is

cursor cdate(date1 date,date2 date) is
	select * from bom where lower(status) like '%prod%' and
	(effective_date_date between date1 and date2)
	order by part_no asc, pca asc,r_level desc;
day1 date;
day2 date;
tag char(1);

words varchar2(100);
begin
	day(word_tab(1),day1,tag);
	if (day1 = NULL) then return; end if;
	if (date_tag = 'b') then
		day(word_tab(2),day2,tag);
		if (day2 = NULL) then return; end if;
		if (tag = 'y') then
			day2 := to_date('3112'||to_char(day2,'yy'),'ddmmyy');
		elsif (tag = 'm') then
			day2 := to_date(to_char(last_day(day2),'dd')||to_char(day2,'mmyy'),'ddmmyy');
		end if;
		words := 'Between <b>'||to_char(day1,'mm/dd/yy')||'</b> And <b>'||
			to_char(day2,'mm/dd/yy')||'</b>';
	elsif (date_tag = 'o') then
		if (tag = 'y') then
			day2 := to_date('3112'||to_char(day1,'yy'),'ddmmyy');
			words := 'in <b>'||to_char(day1,'yyyy')||'</b>';
		elsif (tag = 'm') then
			day2 := to_date(to_char(last_day(day1),'dd')||to_char(day1,'mmyy'),'ddmmyy');
			words := 'in <b>'||to_char(day1,'Month yy')||'</b>';
		else
			day2 := day1 + 1;
			words := 'on <b>'||to_char(day1,'mm/dd/yy')||'</b>';
		end if;
	elsif (date_tag = '>') then
		day2 := to_date('2050','yyyy');
		words := 'After <b>'||to_char(day1,'mm/dd/yy')||'</b>';
	else
		day2 := day1;
		day1 := to_date('1990','yyyy');
		words := 'Before <b>'||to_char(day2,'mm/dd/yy')||'</b>';
	end if;

	htp.p('<center><table border=1 align=center>');

	h_caption := 'for all PCAs '||htf.bold(words);
	htp.p('<caption>Current All BoM Production Documents');
	htp.p(h_caption||'</caption>');
	htp.p(tr(th('Part No.')||th('PCA')||th('Effective Date(EST)')||th('Part Family')||th('Unit Cost')||
	th('Item Type')||th('Purchased Item')||th('Revise Level')||th('Description')||th('Creation Date(EST)')));
	open cdate(lo,hi,day1,day2);
	loop
			fetch cdate into two;
	        	exit when cdate%NOTFOUND;
			h_time := to_char(two.effective_date,'mm/dd/yy, hh24:mi');

			if (two.p_family IS NULL) then
				h_family  := '<br>';
			else
				h_family  := two.p_family;
			end if;
			if (two.s_cost IS NULL) then
				h_cost  := 0.;
			else
				h_cost := two.s_cost;
			end if;
			if (two.description IS NULL) then
				h_desc := '<br>';
			else
				h_desc := two.description;
			end if;
			bom_list(two.part_no,
				two.pca,h_time,h_family,h_cost,
				two.b_type,two.p_item,two.r_level,h_desc,two.revise_date);
	end loop;
	close cdate;

	htp.p('</table></center>');		
end;

procedure day(str varchar2,result out date,tag in out char) is
i integer := 1;
one varchar2(8) := null;
two varchar2(8) := null;
three varchar2(8) := null;
n integer;
j integer := 1;
string varchar2(100);
begin
	string := str;
	while ( string IS NOT NULL and (substr(string,1,1) = '/')) loop
		string := substr(string,2);
	end loop;
	n := length(string);
	while ((i <= n) and (substr(string,i,1) != '/')) loop
		i := i + 1;
	end loop;
	one := substr(string,1,i-1);
	if (i > n) then
		tag := 'y';
	else
		string := substr(string,i+1);
		n := length(string);
		i := 1;
		while (i <= n and (substr(string,i,1) != '/')) loop
			i := i + 1;
		end loop;
		two := substr(string,1,i-1);
		if (i > n) then
			tag := 'm';
		else
			string := substr(string,i+1);
			n := length(string);
			i := 1;
			while (i <= n and (substr(string,i,1) != '/')) loop
				i := i + 1;
			end loop;
			three := substr(string,1,i-1);
			tag := 'd';
		end if;
	end if;		

	-- check the validated date format
	if (tag = 'y') then
		i := length(one);
		one := substr(one,-2,2);
		result := to_date('0101'||one,'ddmmyy');
	else
		one := lpad(one,2,'0');
		if ((to_number(one) < 1) or (to_number(one) > 12)) then
			htp.p('<center>Date format <b>error</b>2: <b>'||str||'</b></center><p>');
			result := NULL;
		else
		 	if (tag = 'm') then
				two := substr(two,-2,2);
				result := to_date(one||two,'FXFMmmyy');
			else
				two := lpad(two,2,'0');
				if (to_number(two) > to_char(last_day(to_date(one,'mm')),'dd')) then
					htp.p('<center>Date format <b>error</b>2: <b>'||str||'</b></center><p>');
					result := NULL;
					return;
				end if;
				i := length(three);
				if (i <= 4) then
					three := substr(three,-2,2);
					result := to_date(one||two||three,'FXFMmmddyy');
				end if;
			end if;
		end if;
	end if;
end;	

end carrier;
/
show errors;
/