Monday, 29 April 2024

Date of Pkg

 CREATE OR REPLACE package SMERP2122.date_of as


function calendar_first_date(p_mod varchar2 , p_cal varchar2) return date;
function calendar_last_date(p_mod varchar2 , p_cal varchar2) return date;
function first_date_monthno(p_month number, p_year number) return date;
function last_date_monthno(p_month number, p_year number) return date;
function system_first_date return date;
function system_last_date return date;
function system_current_dayno return number;
function system_last_dayno return number;

function txn return date;   --- Transaction Date
function txndate(p_date date)  return date;   --- Transaction Date
function month_first_date(p_date date) return date;
function month_last_date(p_date date) return date;
function month_last_dayno(p_date date) return number;
function previous_month_first_date(p_date DATE) RETURN DATE;
function previous_month_last_date(p_date DATE) RETURN DATE;
function allowed_first_date(p_date date ) return date;
function allowed_last_date ( p_date date )return date;
function current_date_no (p_date date) return number;
function day_no_into_name (p_no number) return varchar2;
function name_into_day_no (p_name varchar2) return number;
function nextday_is_rest (p_date date, eid number) return boolean;
function backday_is_rest (p_date date, eid number) return boolean;
function today_is_rest (p_date date, eid number) return boolean;
procedure rest_add (p_date date, eid number) ;
procedure rest_del (p_date date, eid number) ;
function next_restday_no (p_date date) return number;
function back_restday_no (p_date date) return number; 
function check_rest_allocated (p_date date, eid number) return boolean;
function present_dz(p_from date , p_too date , p_shift varchar2, p_org number, p_unit number, p_deptt number , p_sec number) return varchar2 ;
function present_cost(p_from date , p_too date , p_shift varchar2, p_org number, p_unit number, p_deptt number , p_sec number) return number ;
function made_date(p_dd varchar2, p_hh number, p_mi number , p_ss number, p_am varchar2) return date;
function date_making(p_dd varchar2, p_hh number, p_mi number , p_ss number, p_am varchar2) return date;

function fy(p_date date) return varchar2;
function fy(p_date date, p_mod varchar2 := 'ACCOUNTS',  p_cal varchar2) return varchar2;
function Sfy(p_date date, p_mod varchar2 := 'ACCOUNTS',  p_cal varchar2) return varchar2;
function Sfy_from_fy(P_fy varchar2) return varchar2;


function made_first_date(p_month number , p_year number) return date;
function made_last_date(p_month number , p_year number) return date;
function period_status(p_date date ) return boolean;
function month_days(p_date date ) return number;
function month_name(p_month varchar2 ) return varchar2 ;
function month_full_name(p_date date ) return varchar2 ;

function previous_month( p_month number , p_year number ) return number;
function previous_year( p_month number , p_year number ) return number;

function previous_year_date( p_date date) return date;
function is_sunday( p_date date) return varchar2;
function get_dispatch_date(p_day number, p_date date) return varchar2;
function is_period_close(p_date date, p_unit number ) return varchar2;
function get_pkg_po_first_date(p_date date, p_unit number ) return date;
-- 
function doc_Date_greater( table_name varchar2 , doc_col varchar2 , table_doc varchar2  , table_col varchar2 , from_Date date ) return varchar2;

function working_days( p_from date , p_too date ) return number;
function month_remain_days  return number;
function month_days_passed  return number;
function get_days(p_from date , p_too date )  return number;
function get_month_remain_days(p_too date )  return number;

end;
/

/***************************************************************************************************************/

CREATE OR REPLACE package body SMERP2122.date_of as

function calendar_first_date(p_mod varchar2 ,  p_cal varchar2) return date as
 cnt number;
 retval date;
begin
  select count(*) into cnt from SMF_PERIODS a where a.MODULE = p_mod and FY_NAME = p_cal;
  if nvl(cnt,0) <> 0 then
    select min(start_date) into retval from SMF_PERIODS a where a.MODULE = p_mod and FY_NAME = p_cal and rownum =1 ;
  else
    retval := trunc(sysdate);
  end if;
  
  return retval;  
end;
 
function calendar_last_date(p_mod varchar2 , p_cal varchar2) return date as
 cnt number;
 retval date;
begin
  select count(*) into cnt from SMF_PERIODS a where a.MODULE = p_mod and FY_NAME = p_cal;
  if nvl(cnt,0) <> 0 then
    select max(end_date) into retval from SMF_PERIODS a where a.MODULE = p_mod and FY_NAME = p_cal   ;
  else
    retval := trunc(sysdate);
  end if;
  
  return retval;  
end;


function first_date_monthno(p_month number, p_year number) return date as
retval date;
begin

select to_date('01/'||p_month||'/'||p_year,'DD-MM-RRRR') 
into retval from dual;
return retval;
end;

function last_date_monthno(p_month number, p_year number) return date as
retval date;
begin

select last_day(to_date('01/'||p_month||'/'||p_year,'DD-MM-RRRR')) 
into retval from dual;
return retval;

end;

function system_first_date return date as
begin
return trunc(sysdate,'MM');
end;


function system_last_date return date as
begin
return last_day(sysdate);
end;


function system_current_dayno return number as
begin
return to_char(sysdate,'DD');
end;
 

function system_last_dayno return number as
begin
return to_char(last_day(sysdate),'DD');
end;

function txn  return date as
retval date := trunc(sysdate);
begin
if to_number(to_char(sysdate,'HH24MI')) between 00 and 0400 then
retval := trunc(sysdate) -1 ;
end if;  
return retval;
end;
 
function txndate(p_date date)  return date as   --- Transaction Date 
retval date :=  p_date;
begin
if to_number(to_char(p_date,'HH24MI')) between 00 and 0400 then
retval := trunc(p_date) -1 ;
end if;  
return retval;
end;


function month_first_date(p_date date) return date as
begin
return trunc(p_date,'MM');
end;

function month_last_date(p_date date) return date as
begin
return last_day(p_date);
end;

function month_last_dayno(p_date date) return number as
begin
return to_char(last_day(p_date),'DD');
end;

FUNCTION previous_month_first_date(p_date DATE) RETURN DATE as
retval date;
begin
    select trunc(trunc(p_date,'MM')-1,'MM') into retval from dual;
    return retval; 
end;

FUNCTION previous_month_last_date(p_date date) RETURN DATE as
retval date;
begin
    select  trunc(p_date,'MM')-1  into retval from dual;
    return retval; 
end;

function allowed_first_date(p_date date ) return date as
begin
return trunc(sysdate,'MM');
end;

function allowed_last_date ( p_date date )return date
as
begin
return last_day(sysdate);
end;

---


function current_date_no (p_date date) return number as
retval number;
cnt number;
begin

 select count(*) into cnt  from smf_lookups l where l.lookup_type ='WEEK_DAY' and upper(trim(l.lookup_code)) = trim(to_char(p_date,'DAY'));
 if nvl(cnt,0) > 0 then
  select lookup_value into retval from smf_lookups l where l.lookup_type ='WEEK_DAY' and upper(trim(l.lookup_code)) = trim(to_char(p_date,'DAY'));
 else
  retval := 5;
 end if;
 return retval;
end;


function day_no_into_name (p_no number) return varchar2 as
retval varchar2(20);
cnt number;
begin

 select count(*) into cnt  from smf_lookups l where l.lookup_type ='WEEK_DAY' and l.lookup_value  = p_no; 
 if nvl(cnt,0) > 0 then
  select lookup_code  into retval from smf_lookups l where l.lookup_type ='WEEK_DAY' and l.lookup_value  = p_no; 
 else
  retval := 'FRIDAY';
 end if;
 
 return retval;
end;

function name_into_day_no (p_name varchar2) return number as
retval number;
cnt number;
begin

 select count(*) into cnt  from smf_lookups l where l.lookup_type ='WEEK_DAY' and upper(l.lookup_code) = upper(p_name);
 if nvl(cnt,0) > 0 then
  select lookup_value into retval from smf_lookups l where l.lookup_type ='WEEK_DAY' and upper(l.lookup_code) = upper(p_name);
 else
  retval := 5;
 end if;
 return retval;
end;



function nextday_is_rest (p_date date, eid number) return boolean as
retval boolean := false;
dayno number;
restday number;
cnt number;
begin
dayno := current_date_no(p_date);
/*
-- select count(*) into cnt from v_employees_master v where   v.old_bar_code = eid;
   
select count(*) into cnt from v_employees_master v where   v.employee_id = eid;
if nvl(cnt,0) <> 0 then  
-- select v.REST_DAY into restday from v_employees_master v where  v.old_bar_code = eid;
select v.REST_DAY into restday from v_employees_master v where  v.employee_id = eid;
 
else
raise_application_error(-20000,'Rest Day is not Defined....');
end if;
 
if dayno+1 = 8 then
dayno := 0;
end if;  
 
if dayno+1 = restday then
   retval := true;
end if;
*/
  return retval;  
end;  


function backday_is_rest (p_date date, eid number) return boolean as
retval boolean := false;
dayno number;
restday number;
cnt number;
begin
dayno := current_date_no(p_date);
--select count(*) into cnt from v_employees_master v where  v.old_bar_code = eid;
/* select count(*) into cnt from v_employees_master v where  v.employee_id = eid;  
if nvl(cnt,0) <> 0 then  
--select v.REST_DAY into restday from v_employees_master v where  v.old_bar_code = eid;
select v.REST_DAY into restday from v_employees_master v where  v.employee_id = eid;  
else
raise_application_error(-20000,'Rest Day is not Defined....');
end if;
 
if dayno-1 = 0 then
dayno := 8;
end if;
 
if dayno-1 = restday then
   retval := true;
end if; */
  return retval;  
end;




function today_is_rest (p_date date, eid number) return boolean as
retval boolean := false;
dayno number;
restday number;
cnt number;
begin
dayno := current_date_no(p_date);
--select count(*) into cnt from v_employees_master v where  v.old_bar_code = eid;
/* select count(*) into cnt from v_employees_master v where  v.employee_id = eid;  
if nvl(cnt,0) <> 0 then  
--select v.REST_DAY into restday from v_employees_master v where  v.old_bar_code = eid;
select v.REST_DAY into restday from v_employees_master v where  v.employee_id = eid;  
else
raise_application_error(-20000,'Rest Day is not Defined....');
end if;
 
if dayno = restday then
   retval := true;
end if;
*/
  return retval;  
end;


procedure rest_add (p_date date, eid number) as
cnt number;
peid number;
wk number;
begin

--select employee_id into peid from per_employees where old_bar_code = eid;
peid := eid;
 
/* select count(*) into cnt from per_rest where EMPLOYEE_ID = peid and REST_DATE = trunc(p_date);
if nvl(cnt,0) = 0 then
select count(*) into wk from PER_ATTANANCE_SUMMARY where cdate = trunc(p_date) and EMPLOYEE_ID = peid;
if nvl(wk,0) = 0 then
insert into per_rest(EMPLOYEE_ID, REST_DATE,  SOURCE, CREATEDDATE,  CREATE_MACHINE, POSTED)
values(peid,trunc(p_date),'A',sysdate,get_machine_name,'Y');
end if;
end if;*/
end;

procedure rest_del (p_date date, eid number) as
cnt number;
peid number;
begin

--select employee_id into peid from per_employees where old_bar_code = eid;
peid  := eid;
/* select count(*) into cnt from per_rest where EMPLOYEE_ID = peid and REST_DATE = trunc(p_date);
if nvl(cnt,0) <> 0 then
delete from per_rest where EMPLOYEE_ID = peid and REST_DATE = trunc(p_date);
end if;
*/ 
end;



function next_restday_no (p_date date) return number as
retval number;
begin
select current_date_no(p_date) into retval from dual;
if retval + 1 = 8 then
retval := 1;
end if;
return retval;
end;

function back_restday_no (p_date date) return number as
retval number;
begin
select current_date_no(p_date) into retval from dual;
if retval - 1 = 0 then
retval := 7;
end if;
return retval;
end;
 

function check_rest_allocated (p_date date, eid number) return boolean as
retval boolean := false;
cnt number;
peid number;
begin

--select employee_id into peid from per_employees where old_bar_code = eid;
peid := eid;
/*
select count(*) into cnt from per_rest where employee_id = peid and rest_date = trunc(p_date);

if nvl(cnt,0) <> 0 then
   retval := true;
end if;
*/
return retval; 
end;



function present_dz(p_from date , p_too date , p_shift varchar2, p_org number, p_unit number, p_deptt number , p_sec number) return varchar2 as
retval varchar2(2000) := '';
begin
/*
for i in ( 
select  decode(designation_name,null,'Un-Defined',designation_name)||'= ' || count(*) dz
from 
(
select v.EMPLOYEE_ID , v.DESIGNATION_CODE,  initcap(v.designation_name) designation_name from  v_employees_master v
where v.organization_id = decode(p_org,null,v.organization_id,p_org)
and  v.unit_id = decode(p_unit,null,v.unit_id,p_unit)
and nvl(DEPTT_CODE,'0') = decode(P_deptt,null,nvl(DEPTT_CODE,'0'),P_deptt)
and nvl(SEC_ID,'0') = decode(P_sec, null, nvl(SEC_ID,'0') , p_sec)
) v  
,  ( select * from PER_ATTANANCE_SUMMARY  
where cdate between p_from and p_too 
and nvl(shift,'M') = decode(P_SHIFT, null , nvl(shift,'M') , P_SHIFT)
) a
where v.EMPLOYEE_ID = a.EMPLOYEE_ID  
group by designation_name
)
loop
retval := retval ||' , '||i.dz;
end loop;*/
return retval;
end; 

function present_cost(p_from date , p_too date , p_shift varchar2, p_org number, p_unit number, p_deptt number , p_sec number) return number  as
retval number;
begin
/*
select  sum(salary_per_day) into retval
from 
(
select v.EMPLOYEE_ID ,(GROSS_SALARY/date_of.system_last_dayno) salary_per_day
from  v_employees_master v
where v.organization_id = decode(p_org,null,v.organization_id,p_org)
and  v.unit_id = decode(p_unit,null,v.unit_id,p_unit)
and nvl(DEPTT_CODE,'0') = decode(P_deptt,null,nvl(DEPTT_CODE,'0'),P_deptt)
and nvl(SEC_ID,'0') = decode(P_sec, null, nvl(SEC_ID,'0') , p_sec)
) v  
,  ( select * from PER_ATTANANCE_SUMMARY  
where cdate between p_from and p_too 
and nvl(shift,'M') = decode(P_SHIFT, null , nvl(shift,'M') , P_SHIFT)
) a
where v.EMPLOYEE_ID = a.EMPLOYEE_ID  ;
*/
return retval;
end;

function made_date(p_dd varchar2, p_hh number, p_mi number , p_ss number, p_am varchar2) return date as
retval date;
strr varchar2(25);
begin
strr := p_dd||' '||p_hh||':'||p_mi||':'||lpad(p_ss,2,'0') ;
 
dbms_output.put_line(strr);
 
select  to_date(strr,'DD-MON-RRRR HH24:MI:SS')
into retval from dual;
return retval;
end;

function date_making(p_dd varchar2, p_hh number, p_mi number , p_ss number, p_am varchar2) return date as
retval date;
strr varchar2(25);
begin
strr := p_dd||' '||p_hh||':'||p_mi||':'||lpad(p_ss,2,'0') || ' ' || p_am ;
 
dbms_output.put_line(strr);
 
select  to_date(strr,'DD-MON-RRRR HH:MI:SS AM')
into retval from dual;
return retval;
end;

function fy(p_date date) return varchar2 as
retval varchar2(20);
begin
  select c.DOC_YEAR into retval from calendar c where  cdate = trunc(p_date);
  
  --if retval = '2022-21-1' then
--     retval := '2022-23-1' ;
--  end if;   
  
  return retval;
end;

function fy(p_date date, p_mod varchar2 := 'ACCOUNTS',  p_cal varchar2) return varchar2 as
retval varchar2(20);
cnt number;
begin
  select FY_NAME into retval from SMF_PERIODS c where  MODULE = p_mod 
  and CALENDAR_NAME = p_cal
  and trunc(p_date) between START_DATE and  END_DATE and unit_id = 1 and rownum = 1; --and STATUS ='O';
  return retval;
end;

function Sfy(p_date date, p_mod varchar2 := 'ACCOUNTS',  p_cal varchar2) return varchar2 AS
retval varchar2(20);
cnt number;
begin
  select SHORT_FY_NAME into retval from SMF_PERIODS c where  MODULE = p_mod 
  and CALENDAR_NAME = p_cal
  and TRUNC(p_date) between START_DATE and  END_DATE and rownum = 1; --and STATUS ='O';
  return retval;
end;


function Sfy_from_fy(P_fy varchar2) return varchar2 as
retval varchar2(20);
cnt number;
begin
select SHORT_FY_NAME into retval from (
select distinct SHORT_FY_NAME from SMF_PERIODS  where fy_name = P_fy)
where rownum = 1
;
return retval;
end;
 
function made_first_date(p_month number , p_year number) return date as
retval date;
begin
select to_date('01'||'-'||p_month||'-'||p_year,'DD-MM-RRRR ') 
into retval from dual;
return retval;
end;

function made_last_date(p_month number , p_year number) return date as
retval date;
begin
select to_date('01'||'-'||p_month||'-'||p_year,'DD-MM-RRRR ') 
into retval from dual;
 
select last_day(retval) into retval from dual;
return retval;
end;

function period_status(p_date date ) return boolean as
retval boolean;
cnt number;
begin
 
select count(*) into cnt from smf_periods where STATUS='O' and 
TRUNC(p_date) between START_DATE and  END_DATE;
 
if nvl(cnt,0) = 0 then
retval := false;
else
retval := true;
end if;
return retval;
 end ;
  

function month_days(p_date date ) return number as
 retval varchar2(20);
begin
  select to_char(last_day(sysdate),'dd') into retval from dual ;
  return retval;
end;

function month_name(p_month varchar2 ) return varchar2 AS
retval varchar2(200);
begin
select LOOKUP_VALUE into retval from SMF_LOOKUPS WHERE LOOKUP_TYPE = 'MONTH_NAME' AND LOOKUP_CODE = p_month;
return retval;
end;


function month_full_name(p_date date ) return varchar2 AS
retval varchar2(200);
begin
select upper(to_char(to_date(p_date, 'dd-mon-yyyy'),'month')) into retval from dual;
return retval;
end;



function previous_month( p_month number , p_year number ) return number as
retval number;
begin
    retval := p_month -1 ;
    if retval <= 0 then
        retval:= 12;
    end if;
    return retval;
end ;

function previous_year( p_month number , p_year number ) return number as
retval number;
begin
    retval := p_month -1 ;
    if retval <= 0 then
        retval:= p_year -1;
    else
        retval:= p_year;    
    end if;
    return retval;
end ;
     
function previous_year_date( p_date date) return date as
retval date;
begin
    SELECT add_months(p_date, -12) into retval FROM DUAL;
    return retval;
end;

function is_sunday( p_date date) return varchar2 as
dd varchar2(10);
retval varchar2(10) := 'NO';
begin
    SELECT TRIM(to_char(p_date,'DAY')) into dd FROM DUAL;
    if dd = 'SUNDAY' then
        retval :='YES';
    end if;
    return retval;
   
end;


function get_dispatch_date(p_day number, p_date date) return varchar2 as
retval varchar2(10);
p_calc number;
p_monthdays number;
newdat date ;
strr varchar2(200);
dated date;
begin

    if nvl(p_day,0) between 1 and 31 then

        dated := p_date;
        p_calc := p_day +2 ;
        p_monthdays := month_days(dated);
        if p_calc > p_monthdays then
            retval := ( p_calc -p_monthdays ) + 1;
            dated := add_months(p_date,1);
        else
            retval :=  p_calc;
        end if;
        strr := to_char(lpad(retval,2,'0'))||'-'||to_char(dated,'MM')||'-'||to_char(p_date,'RRRR');
        newdat := to_date(strr,'DD-MM-RRRR');
        
        if is_sunday(newdat) ='YES' then
            newdat := newdat + 1;
        end if;
        
        retval := to_char(newdat,'DD-MM-RR');
    
    end if;
    
return retval;
end;


function is_period_close(p_date date, p_unit number ) return varchar2 as
cnt number;
retval varchar2(20);
begin
    select count(*) into cnt from smf_periods where unit_id = p_unit and p_date between start_date and end_date;
    if nvl(cnt,0) = 0 then
        select count(*) into cnt from smf_periods where  p_date between start_date and end_date and unit_id = 1;
        if nvl(cnt,0) <> 0 then
            select status into retval from smf_periods where  p_date between start_date and end_date and unit_id = 1;
        end if;
    else
           select status into retval from smf_periods where  p_date between start_date and end_date and unit_id = p_unit;
    end if;
    
    return retval;
end;
    
            
function get_pkg_po_first_date(p_date date, p_unit number ) return date as
retval date;
cnt number;
begin
        
        if p_unit = 19 then
            if to_number(to_char(p_date,'DD')) > 21 then
                retval := month_first_date(p_date) + 21;
            else
                retval := month_first_date(month_first_date(p_date) - 1)+21 ;
            end if;

        else
            if to_number(to_char(p_date,'DD')) > 24 then
                retval := month_first_date(p_date) + 24;
            else
                retval := month_first_date(month_first_date(p_date) - 1)+24 ;
            end if;
        end if;
        
return retval;
end;
                

function doc_Date_greater( table_name varchar2 , doc_col varchar2 , table_doc varchar2  , table_col varchar2 , from_Date date )  return varchar2 as
strr varchar2(2000);
cnt number;
retval varchar2(20) := 'NO';
dt date;
begin

    strr := ' select '|| table_col || ' from ' || table_name || ' where ' || doc_col || ' = '''  ||table_doc ||'''';
    
    
    --dbms_output.put_line(strr);
    
    
    EXECUTE IMMEDIATE strr INTO dt;
    
    --dbms_output.put_line(to_char(dt));
    
        
    if  dt > from_date   then
        retval := 'YES';
    end if;

    if from_Date is null then
        retval := 'YES';
    end if;


    return retval;
end;
    
function working_days( p_from date , p_too date ) return number as
retval number;
begin
    select count(*) into retval from calendar where cdate between p_from  and  p_too and date_of.is_sunday(cdate) ='NO';
    return retval;
end;


function month_remain_days  return number as                               
retval number;
begin
    select to_char(last_day(sysdate),'dd') - to_char(sysdate,'dd') into retval from  dual ;
    return retval;
end;

function month_days_passed  return number as
retval number;
begin
    select to_char(sysdate,'dd') - to_char(trunc(sysdate,'MM'),'dd') into retval from  dual ;
    return retval;
end;

function get_days(p_from date , p_too date )  return number as
retval number;
begin
    select to_char(p_too,'dd') - to_char(trunc(p_from,'MM'),'dd')+1 into retval from  dual ;
    return retval;
end;

function get_month_remain_days(p_too date )  return number as
retval number;
begin
    select to_char(last_day(p_too),'dd') - to_char(p_too,'dd') into retval from  dual ;
    return retval;
end;

end;
/
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Check Internet Connectivity on Local System

if (!navigator.onLine) {     showOfflineAlert(); } else {     // Set a strict 3-second network timeout threshold     const controller = new ...