

 create function ddf_get_holtblno

  (as_emp_code char,as_date date)

  return char is lc_hol_tblno employee.hol_tblno%type;
  A number(14,3);
  B number(14,3);
  W employee.work_site%type;

begin

  select nvl(count(*),0) into A from emp_employ_events

  where emp_code = AS_EMP_CODE

  and confirmed = 'Y';

  if (A > 0) then

    select nvl(count(*),0) into B from emp_employ_events

      where emp_code = AS_EMP_CODE

      and eff_date = (

        select max(eff_date) from emp_employ_events

          where emp_code = AS_EMP_CODE

          and confirmed =  'Y'

          and eff_date <= AS_DATE)

      and confirmed = 'Y';

    if (B > 0) then

      select aa.hol_tblno__to into lc_hol_tblno from emp_employ_events aa

        where aa.emp_code = AS_EMP_CODE

        and aa.confirmed = 'Y'

        and aa.tran_id =

          (

          select max(bb.tran_id) from emp_employ_events bb

            where bb.emp_code = aa.emp_code

            and bb.eff_date =

              (

              select max(cc.eff_date) from emp_employ_events cc

                where cc.emp_code = aa.emp_code

                and cc.eff_date <= AS_DATE

                and cc.confirmed = 'Y'

              )

            and bb.confirmed = 'Y'

          );

    else

      select aa.hol_tblno__fr into lc_hol_tblno from emp_employ_events aa

        where aa.emp_code = AS_EMP_CODE

        and aa.confirmed = 'Y'

        and aa.tran_id =

          (

          select min(bb.tran_id) from emp_employ_events bb

            where bb.emp_code = aa.emp_code

            and bb.eff_date =

              (

              select min(cc.eff_date) from emp_employ_events cc

                where cc.emp_code = aa.emp_code

                and cc.eff_date > AS_DATE

                and cc.confirmed = 'Y'

              )

            and bb.confirmed = 'Y'

          );

    end if;

  end if;

  if length(ltrim(rtrim(lc_hol_tblno))) = 0 or lc_hol_tblno is null then

    select hol_tblno,work_site into lc_hol_tblno,W from employee

      where emp_code = AS_EMP_CODE;

    if length(ltrim(rtrim(lc_hol_tblno))) = 0 or lc_hol_tblno is null then

      select hol_tblno into lc_hol_tblno from site

        where site_code = W;

    end if;

  end if;

  return lc_hol_tblno;

end;
/

create function ddf_get_emp_holiday

(AS_EMP_CODE CHAR,AS_PRD_CODE_FR CHAR,AS_PRD_CODE_TO CHAR,AS_EMP_WORK_DATE DATE)

return char is

a_hol_date char(11);

begin

select distinct to_char(hol_date,'DD-MON-YYYY') into a_hol_date from holiday

where hol_tblno = ddf_get_holtblno(AS_EMP_CODE,AS_EMP_WORK_DATE)

and hol_date = AS_EMP_WORK_DATE;

return a_hol_date;

end;
/

INSERT INTO messages (
	msg_no,
	msg_str,
	msg_descr,
	msg_type,
	msg_opt,
	msg_time,
	alarm,
	err_source,
	chg_date,
	chg_user,
	chg_term,
	override_input,
	mail_option
)
 VALUES (
	'VTNULDATEF',
	'Null Date ',
	'Please enter date for Physcial count Schedule.',
	'E',
	'Y',
	NULL,
	NULL,
	NULL,
	NULL,
	'BASE      ',
	'BASE      ',
	NULL,
	NULL); 

INSERT INTO messages (
	msg_no,
	msg_str,
	msg_descr,
	msg_type,
	msg_opt,
	msg_time,
	alarm,
	err_source,
	chg_date,
	chg_user,
	chg_term,
	override_input,
	mail_option
)
 VALUES (
	'VTNOTCURRD',
	'Not today date ',
	'Please select today date',
	'E',
	'Y',
	NULL,
	NULL,
	NULL,
	NULL,
	'BASE      ',
	'BASE      ',
	NULL,
	NULL);
commit;

CREATE TABLE ITEM_PROCESS
(
 ITEM_CODE                                  CHAR(10) NOT NULL,
ANALYSIS_CLASS                                     CHAR(1),
 TRAN_DATE                                          DATE,
 SITE_CODE                                          CHAR(10),
 LOC_CODE                                           CHAR(10),
PRIORITY_SEQ                                       NUMBER(3),
 CHG_USER                                           CHAR(10),
 CHG_TERM                                           CHAR(15),
 CHG_DATE                                           DATE
 
 
)
;

CREATE TABLE DATESCHEDULE
(
 analysis_class                                  CHAR(1),
 start_date                                        DATE,
 end_date                                          DATE,
 working_days                                      NUMBER(3),
 no_of_item                                           NUMBER(2),
 next_item_date                                      DATE

);
commit;
