Kamran Shahzad: April 2024

Monday, 29 April 2024

Ledger Restrict Function

CREATE OR REPLACE function SMERP2122.get_ledger_restrict(p_unit number , p_user number ) return varchar2 as
retval varchar2(4000);
cnt number;
alist varchar2(4000);
lno number := 0;
begin
select count(*) into cnt from
(
select a.txn_ide , a.user_id , segment6 , segment7, segment8, segment9, segment10, segment11
from SMF_USER_LEDGER_M a , SMF_USER_LEDGER_SEG11_D b
where a.txn_ide = b.txn_ide and a.user_id = p_user
) ;

retval := ' segment4 = ' || p_unit ;
if nvl(cnt,0) <> 0 then
for i in (
select a.txn_ide , a.user_id , segment6 , segment7, segment8,segment9, segment10, segment11
from SMF_USER_LEDGER_M a , SMF_USER_LEDGER_SEG11_D b
where a.txn_ide = b.txn_ide and a.user_id = p_user
)
loop
if nvl(i.segment6,0) <> 0 then
retval := retval || ' and ( segment6 not in ( ' || i.segment6 || ') ) ' ;
end if;
if nvl(i.segment7,0) <> 0 then
retval := retval || ' and ( segment7 not in (' || i.segment7 || ' ) ) ';
end if;
if nvl(i.segment8,0) <> 0 then
retval := retval || ' and ( segment8 not in ( ' || i.segment8 || ' )) ';
end if;
if nvl(i.segment9,0) <> 0 then
retval := retval || ' and ( segment9 not in ( ' || i.segment9 || ' ) ) ';
end if;
if nvl(i.segment10,0) <> 0 then
retval := retval || ' and ( segment10 not in ( ' || i.segment10 || ' ) ) ' ;
end if;
if nvl(i.segment11,0) <> 0 then
retval := retval || ' and ( segment11 not in ( ' || i.segment11 || ' ) ) ' ;
end if;

if retval is not null then
retval := ' ( ' || retval || ' ) ' ;
end if ;
end loop ;
end if;

select count(*) into cnt from
(
select a.txn_ide , a.user_id , accode
from SMF_USER_LEDGER_M a , SMF_USER_LEDGER_SEG11_D b , SMF_USER_LEDGER_CODE_D c
where a.txn_ide = b.txn_ide
and b.txn_ide = c.txn_ide
and a.user_id = p_user
);

if nvl(cnt,0) <> 0 then
for i in ( select a.txn_ide , a.user_id , accode
from SMF_USER_LEDGER_M a , SMF_USER_LEDGER_SEG11_D b , SMF_USER_LEDGER_CODE_D c
where a.txn_ide = b.txn_ide
and b.txn_ide = c.txn_ide
and a.user_id = p_user
)
loop
if lno = 0 then
--alist := ' AND ACCODE NOT IN ( ' || I.ACCODE ;
alist := 'where ACCODE NOT IN ( ' || I.ACCODE ;
lno := lno + 1;
end if;
if lno > 0 then
alist := alist || ' , ' || I.ACCODE ;
end if;
end loop;
alist := alist || ' ) ';
end if;

-- retval := retval || alist ;
retval := alist ;
return retval;
end;
/

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

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;

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;

Document OF PKG

CREATE OR REPLACE PACKAGE SMERP2122.Document_Of AS

FUNCTION IGP(P_UNIT NUMBER, P_SID NUMBER := '0' , P_LOC NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION OGP(P_UNIT NUMBER , P_SID NUMBER := '0' , P_LOC NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION IUS(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION IUP(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION INS(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION GRN(P_UNIT NUMBER , P_SID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION DMD(P_UNIT NUMBER, P_UID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ISS(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ISS_ADJ(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION dbnote(P_UNIT NUMBER , p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION RTN(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY varchar2) RETURN VARCHAR2;

FUNCTION Prod(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION Prod_floor(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PPL(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2;

-- Production Receiving

FUNCTION PRcv(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PTN(P_UNIT NUMBER, P_SID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION REJ(P_UNIT NUMBER, P_SID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ssv(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION WHIGP(P_UNIT NUMBER, P_SID NUMBER := '0' ) RETURN VARCHAR2;

FUNCTION TRN(P_UNIT NUMBER , P_SID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- unit sale invoice

FUNCTION USI(P_UNIT NUMBER , P_SID NUMBER := '0' , p_loc NUMBER , P_fy VARCHAR2 ) RETURN VARCHAR2;

-- unit sale invoice summary

FUNCTION USS(P_UNIT NUMBER , P_SID NUMBER := '0' , p_loc NUMBER ) RETURN VARCHAR2;

--FUNCTION jvno(p_unit NUMBER := 1 ) RETURN VARCHAR2;

FUNCTION PRO_SCH(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION PRO_UNIT_SCH(P_UNIT NUMBER ) RETURN VARCHAR2;

-- Accounts New

FUNCTION jvno (p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION jvno_hired (p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION crvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION tmp_crvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION drvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION tmp_drvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION dpvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION pjvvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION pjvvno_Type(p_unit NUMBER, p_fy VARCHAR2 , p_type varchar2) RETURN VARCHAR2;

FUNCTION onlno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ptvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION IMP(p_unit NUMBER, p_fy VARCHAR2 , p_type varchar2 ) RETURN VARCHAR2;

FUNCTION bpvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION brvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION cash_bpvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION cash_brvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION btvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION bxvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION tmp_bxvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

-- Sales Area

FUNCTION ordno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ordnoE(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ordnoD(p_unit NUMBER, p_fy VARCHAR2, P_CITY Varchar2 ) RETURN VARCHAR2; --Distributor

FUNCTION tmp_ordnoD(p_unit NUMBER, p_fy VARCHAR2, P_CITY Varchar2 ) RETURN VARCHAR2;

FUNCTION sivno(p_unit NUMBER, p_fy VARCHAR2 , p_mtl_store varchar2 := 0) RETURN VARCHAR2;

FUNCTION srvno(p_unit NUMBER, p_fy VARCHAR2 , p_mtl_store varchar2 := 0) RETURN VARCHAR2;

FUNCTION srtnvno(p_unit NUMBER, p_fy VARCHAR2) RETURN VARCHAR2;

FUNCTION slvno(p_unit NUMBER, p_fy VARCHAR2 , p_loc number := 0) RETURN VARCHAR2;

FUNCTION svrno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ssvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION siuno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

-- Payment Slip No

FUNCTION pslipno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

------- WH

FUNCTION Wh_Order(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- Return

FUNCTION WHRTN(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- PARTY Return

FUNCTION WHPTN(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- PO CONTRACT

FUNCTION pocontract(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

-- SALES FORE CAST

FUNCTION FORECAST(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

-- REJECTION INVOICE NO

FUNCTION REJECTION(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

-- PAKCGAE COST NO

FUNCTION PKG_COST(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

-- EXPERT COST NO

FUNCTION PKG_COST_EXP(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

--- PO

FUNCTION PO_REQ(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PO_NO(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PO_NO (P_UNIT NUMBER , P_SID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

-- PKG BOM

FUNCTION BOM_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION BOM_ORDER_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION BOM_JOB_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION BOM_LAY_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION BOM_PLAN_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- PDS

FUNCTION PDS_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

--- FREIGHT - PJV - FREIGHT

FUNCTION PJF_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION FTV_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- spcies , conversion

FUNCTION CONX_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- Audit No

FUNCTION WH_AUDIT_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION LOCX_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION FGS_REQ_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION ticket_no(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION WLOCX_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2; -- WARE HOUSE LOCATIONS

FUNCTION ISS_TO_PROD(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2; -- ISSUE TO PRODUCTION

FUNCTION BOX_SALEORDER(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2; -- ISSUE TO PRODUCTION

FUNCTION BOX_JOBCARD(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2; -- ISSUE TO PRODUCTION

FUNCTION DISPATCHPLAN(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION postages(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION token(P_UNIT NUMBER , p_date date ) RETURN VARCHAR2;

FUNCTION QOT(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION WOD(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION MOD_CIV(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION WOD_CIV(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION FLT_BILL(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION vbook(P_UNIT NUMBER ) RETURN VARCHAR2;

FUNCTION LABORBREAK(P_UNIT NUMBER , P_FY VARCHAR2) RETURN VARCHAR2;

---

FUNCTION PROD_PKG(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PKGS_DIE(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PKGS_FG(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PKGS_WP(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PKGS_LAM(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION RDR(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

function downtine(p_unit number , p_fy varchar2) return varchar2;

FUNCTION RRJ(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2;

---

FUNCTION PROD_SEMI(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2;

--ADVANCE SALE FORECAST

FUNCTION ADV_FORECAST(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2;

FUNCTION counter(p_unit NUMBER, table_name VARCHAR2 ) RETURN VARCHAR2;

FUNCTION counter(p_unit NUMBER, table_name VARCHAR2, P_DOC varchar2 ) RETURN VARCHAR2;

FUNCTION col_counter(p_unit NUMBER, table_name VARCHAR2, p_col varchar2 ) RETURN VARCHAR2;

-- QA PKGS

FUNCTION QA_PAPER(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION QC_PROD (P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2;

-- cph trading

FUNCTION CPH_REJ (P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2;

--- PL CLOSE NO

FUNCTION PL_CLOSE (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

-- PK_ISO

FUNCTION PKG_ISO (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

--PROD TGT

FUNCTION PROD_TXT (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION PAY_REQUEST (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 ;

function task_no (p_unit number, p_dated date) return varchar2;

function QRCV (P_UNIT NUMBER , P_SID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION FLR_PLAN(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION FLR_ISS(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

---

-- Purchase requsition plan ( PRP )

FUNCTION PRP(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION HSE_IA(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2;

FUNCTION HSE_MDR(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 ;

FUNCTION HSE_ISD(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 ;

FUNCTION HSE_WP(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 ; -- WORK PERMIT

END;

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

CREATE OR REPLACE PACKAGE BODY SMERP2122.Document_Of AS

FUNCTION IGP(P_UNIT NUMBER, P_SID NUMBER := '0' , P_LOC NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'IGP';

UID CHAR(3) ;

fy VARCHAR(25) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

retval VARCHAR2(40);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF Name_Of.location_type(NVL(P_LOC,1)) = 'F.G.STORE' THEN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(IGP_NO,-6)),0)+1 INTO cnt FROM INV_IGP_M WHERE unit_id = p_unit AND fin_year = fy AND IGP_LOCATION = p_loc ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-FG-'||p_loc||'-'||sfy||'-'||cntr;

ELSIF Name_Of.location_type(NVL(P_LOC,1)) = 'WAREHOUSE' THEN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(IGP_NO,-6)),0)+1 INTO cnt FROM INV_IGP_M WHERE unit_id = p_unit AND fin_year = fy AND IGP_LOCATION = p_loc ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-WH-'||UID||'-'||sfy||'-'||cntr;

ELSIF Name_Of.location_type(NVL(P_LOC,1)) = 'SCRAP STORE' THEN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(IGP_NO,-6)),0)+1 INTO cnt FROM INV_IGP_M WHERE unit_id = p_unit AND fin_year = fy AND IGP_LOCATION = p_loc ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-SCP-'||UID||'-'||sfy||'-'||cntr;

ELSE

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(IGP_NO,-6)),0)+1 INTO cnt FROM INV_IGP_M WHERE unit_id = p_unit AND fin_year = fy

AND nvl(IGP_LOCATION,'0') NOT IN ( SELECT LOCATOIN_ID FROM SMF_UNIT_LOCATIONS WHERE loc_type ='F.G.STORE') ;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||sfy||'-'||cntr;

SELECT COUNT(*) INTO cnt FROM INV_IGP_M WHERE igp_no = retval;

IF NVL(cnt,0) > 0 THEN

--retval := docid||'-'||UID||'-'||sfy||'-'||LPAD(TO_NUMBER(cnt)+1,6,'0');

retval := NULL;

END IF;

RETURN retval;

END;

FUNCTION OGP(P_UNIT NUMBER , P_SID NUMBER := '0' , P_LOC NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'OGP';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(30);

retval VARCHAR2(30);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF Name_Of.location_type(NVL(P_LOC,0)) NOT IN ( 'F.G.STORE', 'SCRAP STORE' ) THEN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(OGP_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_FY

AND OGP_LOCATION NOT IN ( SELECT LOCATOIN_ID FROM SMF_UNIT_LOCATIONS WHERE loc_type in ( 'F.G.STORE','SCRAP STORE'));

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-' ||sfy||'-'||cntr;

ELSIF Name_Of.location_type(NVL(P_LOC,0)) = 'SCRAP STORE' THEN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--UID := 'SCP';

SELECT NVL(MAX(SUBSTR(OGP_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_FY

AND OGP_LOCATION = P_LOC ;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||'SCP'||'-'||sfy||'-'||cntr;

ELSIF Name_Of.location_type(NVL(P_LOC,0)) = 'F.G.STORE' THEN

if p_loc = 43 then

UID := 'GBK';

SELECT NVL(MAX(SUBSTR(OGP_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_FY AND OGP_LOCATION = p_loc ; -- last max number

else

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(OGP_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_FY AND OGP_LOCATION = p_loc ; -- last max number

end if;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-FG-'||UID||'-'||p_loc||'-'||sfy||'-'||cntr;

END IF;

RETURN retval;

END;

-- INTER UNIT GATE PASS

FUNCTION IUS(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM INT_SAL_M WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'IUS-' || docid || '-1000001';

ELSE

retval:= 'IUS-' || docid ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION IUP(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

BEGIN

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(SI_NO,9,14)+1) INTO BB

FROM wh_igp_m WHERE unit_id = p_unit and fin_year = p_fy ;

IF BB IS NULL THEN

retval := 'IUP-' || docid || '-1000001';

ELSE

retval:= 'IUP-' || docid ||'-'|| BB;

END IF;

RETURN retval;

END;

/*FUNCTION OGP(P_UNIT NUMBER, P_SID NUMBER := '0' ) RETURN VARCHAR2 as

docid char(3) := 'OGP';

uid char(3) ;

fy varchar(7);

nfy varchar(5);

cnt number;

cntr varchar2(10);

begin

select DOC_ID into uid from smf_units where unit_id = p_unit; -- document id

select doc_year into fy from CALENDAR where ndate = to_char(trunc(sysdate),'rrrrmmdd'); -- financial year

select nyear into nfy from CALENDAR where ndate = to_char(trunc(sysdate),'rrrrmmdd'); -- financial year

select nvl(max(substr(OGP_NO,-6)),0)+1 into cnt from INV_OGP_M where unit_id = p_unit AND fin_year = fy and store_id = p_sid ; -- last max number

cntr :=lpad(cnt,6,'0');

return docid||'-'||uid||'-'||nfy||'-'||P_SID||'-'|| cntr;

end;

FUNCTION INS(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'INS';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(INS_NO,-6)),0)+1 INTO cnt FROM INV_INSPECTION_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION GRN(P_UNIT NUMBER, P_SID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'GRN';

UID CHAR(3) ;

fy VARCHAR(25) ;

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR(25);

retval varchar2(50);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

--SELECT nyear INTO nfy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(Time_Of.date_gate(SYSDATE)),'rrrrmmdd'); -- financial year

-- if p_unit = 19 then

SELECT NVL(MAX(SUBSTR(GRN_NO,-6)),0)+1 INTO cnt FROM INV_GRN_M WHERE unit_id = p_unit AND fin_year = p_fy and store_id = p_sid ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||lpad(p_sid,2,'0')||'-'||sfy|| '-'|| cntr;

/*else

SELECT NVL(MAX(SUBSTR(GRN_NO,-6)),0)+1 INTO cnt FROM INV_GRN_M WHERE unit_id = p_unit AND fin_year = p_fy ; --and store_id = p_sid ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||sfy|| '-'|| cntr;

-- end if;

RETURN retval;

END;

FUNCTION DMD(P_UNIT NUMBER, P_UID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'DMD';

UID VARCHAR2(5) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

usrid VARCHAR2(6);

nfy VARCHAR(5);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

-- SELECT nyear INTO nfy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(Time_Of.date_gate(SYSDATE)),'rrrrmmdd'); -- financial year

-- SELECT DOC_YEAR INTO fy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(Time_Of.date_gate(SYSDATE)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(DMD_NO,-6)),0)+1 INTO cnt FROM INV_DMD_M WHERE unit_id = p_unit AND fin_year = P_fy AND user_id = p_uid; -- last max number

cntr := LPAD(cnt,6,'0');

usrid := LPAD(P_UID,5,'0');

RETURN UID||'-'||sfy||'-'||usrid||'-'||cntr;

END;

FUNCTION ISS(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2) RETURN VARCHAR2 AS

docid CHAR(3) := 'ISS';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(40);

cat number;

retval varchar2(50);

BEGIN

if p_unit in ( 19 ) and p_sid = 18 then

if p_sbid = '93' then

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISS_NO,-6)),0)+1 INTO cnt FROM INV_ISS_M WHERE unit_id = p_unit AND fin_year = P_FY and store_id = 18 and iss_date >= '01-jan-2019'

and UPPER(substr(substr(iss_no,-10),1,4)) = '-BX-';

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||sfy||'-BX-' ||cntr;

else

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISS_NO,-6)),0)+1 INTO cnt FROM INV_ISS_M WHERE unit_id = p_unit AND fin_year = P_FY and store_id = 18 and iss_date >= '01-jan-2019'

and substr(substr(iss_no,-10),1,4) = '-18-';

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||sfy||'-18-' ||cntr;

end if;

elsif p_unit in ( 19 ) and p_sid <> 18 then

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISS_NO,-6)),0)+1 INTO cnt FROM INV_ISS_M WHERE unit_id = p_unit AND fin_year = P_FY and store_id <> 18 ;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'|| lpad(P_SID,2,'0') ||'-'||sfy||'-' ||cntr;

else

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISS_NO,-6)),0)+1 INTO cnt FROM INV_ISS_M WHERE unit_id = p_unit AND fin_year = P_FY ;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'|| lpad(P_SID,2,'0') ||'-'||sfy||'-' ||cntr;

end if;

RETURN retval ;

END;

FUNCTION ISS_ADJ(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'ADS';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(40);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISS_NO,-6)),0)+1 INTO cnt FROM INV_ISS_ADJ_M WHERE unit_id = p_unit AND fin_year = P_FY ;

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION dbnote(P_UNIT NUMBER , p_fy VARCHAR2) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(40);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(dr_no,-7)+1) INTO BB

FROM DR_NOTE_M WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DNV-' || docid ||'-'||sfy||'-'|| '-1000001';

ELSE

retval := 'DNV-' || docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION RTN(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY varchar2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'RTN';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy varchar2(30);

retval varchar2(40);

BEGIN

if p_unit in ( 19 ) and p_sid = 18 then

if p_sbid = '93' then

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(RTN_NO,-6)),0)+1 INTO cnt FROM INV_RTN_M WHERE unit_id = p_unit AND fin_year = P_FY and store_id = 18

and substr(substr(RTN_NO,-10),1,4) = '-BX-';

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||sfy||'-BX-' ||cntr;

end if;

else

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(RTN_NO,-6)),0)+1 INTO cnt FROM INV_RTN_M WHERE unit_id = p_unit AND fin_year = p_fy ; --and store_id = p_sid and INV_STORE_ID=P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy|| '-'||cntr;

end if;

return retval;

END;

FUNCTION Prod(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PRD';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

if p_unit in ( 19 ) then

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(pro_NO,-6)),0)+1 INTO cnt FROM PRO_PACKING_M WHERE unit_id = p_unit and fin_year = p_fy ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

else

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(pro_NO,-6)),0)+1 INTO cnt FROM PRO_PACKING_M WHERE unit_id = p_unit AND fin_year = P_FY AND user_id = P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||P_SBID||'-'||cntr;

end if;

END;

FUNCTION Prod_floor(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'PRD';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = P_UNIT; -- document id

SELECT NVL(MAX(SUBSTR(pro_NO,-6)),0)+1 INTO cnt FROM PRO_FLOOR_M WHERE unit_id = P_UNIT AND fin_year = P_FY AND user_id = P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||P_SBID||'-'||cntr;

END;

FUNCTION PPL(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PPL';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(PLAN_NO,-6)),0)+1 INTO cnt FROM WH_PROD_PLAN_M WHERE unit_id = p_unit AND fin_year = P_FY ;

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PRcv(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'RCV';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(pro_NO,-6)),0)+1 INTO cnt FROM PRO_PROD_RCV_M WHERE unit_id = p_unit AND fin_year = P_FY AND STORE_ID = P_SID AND mtl_store_id = P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||P_SBID||'-'||cntr;

END;

FUNCTION PTN(P_UNIT NUMBER, P_SID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PTN';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT doc_year INTO fy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

SELECT nyear INTO nfy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT NVL(MAX(SUBSTR(PTN_NO,-6)),0)+1 INTO cnt FROM INV_PTN_M WHERE unit_id = p_unit AND fin_year = P_fy AND store_id = p_sid AND SUBSTR(PTN_NO,1,3) = docid ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy|| '-'|| cntr;

END;

FUNCTION REJ(P_UNIT NUMBER, P_SID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'REJ';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

SFY VARCHAR2(30);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--SELECT doc_year INTO fy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

--SELECT nyear INTO nfy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT NVL(MAX(SUBSTR(PTN_NO,-6)),0)+1 INTO cnt FROM INV_PTN_M WHERE unit_id = p_unit AND fin_year = P_FY AND SUBSTR(PTN_NO,1,3) = docid ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||Sfy|| '-'|| cntr;

END;

FUNCTION ssv(P_UNIT NUMBER ) RETURN VARCHAR2 AS

retval VARCHAR2(20);

cnt NUMBER;

BEGIN

SELECT COUNT(*) INTO cnt FROM ledd WHERE SUBSTR(vno,1,3) ='SSV';

IF NVL(cnt,0) = 0 THEN

retval:='SSV1000001';

ELSE

SELECT MAX(SUBSTR( vno, -7))+1 INTO retval FROM ledd WHERE SUBSTR(vno,1,3) ='SSV' ; --AND fin_year = P_FY ;

retval := 'SSV'||retval ;

END IF;

RETURN retval;

END;

FUNCTION WHIGP(P_UNIT NUMBER, P_SID NUMBER := '0' ) RETURN VARCHAR2 AS

docid CHAR(3) := 'IGP';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

retval VARCHAR2(40);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT doc_year INTO fy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(IGP_NO,-6)),0)+1 INTO cnt FROM WH_IGP_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||fy||'-'||cntr;

SELECT COUNT(*) INTO cnt FROM INV_IGP_M WHERE igp_no = retval;

IF NVL(cnt,0) > 0 THEN

retval := docid||'-'||UID||'-'||fy||'-'||LPAD(TO_NUMBER(cnt)+1,6,'0');

END IF;

RETURN retval;

END;

FUNCTION TRN(P_UNIT NUMBER , P_SID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'TRN';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

retval VARCHAR2(40);

sfy VARCHAR2(40);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--SELECT doc_year INTO fy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(TRN_NO,-6)),0)+1 INTO cnt FROM INV_TRN_M WHERE unit_id = p_unit AND fin_year = p_fy; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||sfy||'-'||cntr;

SELECT COUNT(*) INTO cnt FROM INV_TRN_M WHERE TRN_NO = retval;

IF NVL(cnt,0) > 0 THEN

retval := docid||'-'||UID||'-'||sfy||'-'||LPAD(TO_NUMBER(cnt)+1,6,'0');

END IF;

RETURN retval;

END;

FUNCTION USI(P_UNIT NUMBER , P_SID NUMBER := '0' , p_loc NUMBER , P_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'SIN';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

retval VARCHAR2(40);

SID NUMBER;

loctype VARCHAR2(20);

sfy VARCHAR2(200);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_sid <>2 THEN

SID := 0;

ELSE

SID := 2;

END IF;

-- SELECT loc_type INTO loctype FROM SMF_UNIT_LOCATIONS WHERE LOCATOIN_ID = p_loc;

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--SELECT doc_year INTO fy FROM CALENDAR WHERE ndate = TO_CHAR(TRUNC(SYSDATE),'rrrrmmdd'); -- financial year

IF SID = 2 THEN

IF loctype= 'F.G.STORE' THEN

SELECT NVL(MAX(SUBSTR(SI_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_fy AND store_id = 2

AND OGP_LOCATION = p_loc;

ELSE

SELECT NVL(MAX(SUBSTR(SI_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_fy AND store_id = 2

AND OGP_LOCATION NOT IN ( SELECT LOCATOIN_ID FROM SMF_UNIT_LOCATIONS WHERE unit_id = p_unit AND LOC_TYPE = 'F.G.STORE' );

END IF;

ELSE

SELECT NVL(MAX(SUBSTR(SI_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_fy AND store_id <> SID; -- last max number

END IF;

SELECT NVL(MAX(SUBSTR(SI_NO,-6)),0)+1 INTO cnt FROM INV_OGP_M WHERE unit_id = p_unit AND fin_year = P_fy and substr(SI_NO,1,3) = 'SIN' ; -- last max number

cntr :=LPAD(cnt,6,'0');

/*IF SID = 2 THEN

IF loctype= 'F.G.STORE' THEN

retval := docid||'-'||UID||'-'||sfy||'-'||LPAD(p_loc,2,'0')||'-'||cntr;

ELSE

retval := docid||'-'||UID||'-'||sfy||'-'||LPAD(SID,2,'0')||'-'||cntr;

END IF;

ELSE

retval := docid||'-'||UID||'-'||sfy||'-'||LPAD(SID,2,'0')||'-'||cntr;

END IF;

SELECT COUNT(*) INTO cnt FROM INV_OGP_M WHERE SI_NO = retval and fin_year = P_FY;

IF NVL(cnt,0) > 0 THEN

retval := docid||'-'||UID||'-'||sfy||'-'||LPAD(SID,2,'0')||'-'||LPAD(TO_NUMBER(cnt)+1,6,'0');

END IF;

retval := docid||'-'||UID||'-'||sfy||'-'||cntr;

RETURN retval;

END;

FUNCTION USS(P_UNIT NUMBER , P_SID NUMBER := '0' , p_loc NUMBER ) RETURN VARCHAR2 AS

docid CHAR(3) := 'SIS';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

retval VARCHAR2(40);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(INVNO,-6)),0)+1 INTO cnt FROM JV_SI WHERE SI_UNIT_ID = p_unit AND si_store_id = p_sid; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'|| P_UNIT ||'-'||P_sid||'-'||cntr;

SELECT COUNT(*) INTO cnt FROM JV_SI WHERE INVNO = retval;

IF NVL(cnt,0) > 0 THEN

retval := docid||'-'|| P_UNIT ||'-'||P_sid||'-'||LPAD(TO_NUMBER(cnt)+1,6,'0');

END IF;

RETURN retval;

END;

FUNCTION PRO_SCH(P_UNIT NUMBER ) RETURN VARCHAR2 AS

retval VARCHAR2(40);

docid CHAR(3) := 'SCH';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

-- SELECT NVL(MAX(SUBSTR(PRO_NO,-6)),0)+1 INTO cnt FROM PRO_SCHEDULE_M WHERE UNIT_ID = p_unit ;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'|| UID || '-'||cntr;

RETURN retval;

END;

FUNCTION PRO_UNIT_SCH(P_UNIT NUMBER ) RETURN VARCHAR2 AS

retval VARCHAR2(40);

docid CHAR(3) := 'UPS';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

-- SELECT NVL(MAX(SUBSTR(PRO_NO,-6)),0)+1 INTO cnt FROM SCH_UNI_M WHERE UNIT_ID = p_unit ;

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'|| UID || '-'||cntr;

RETURN retval;

END;

/* FUNCTION jvno(p_unit NUMBER := 1 ) RETURN VARCHAR2 AS

AA NUMBER;

BB NUMBER;

retval VARCHAR2(30);

BEGIN

SELECT MAX(SUBSTR(JV.VNO,4,10)+1) INTO BB

FROM JV;

IF BB IS NULL THEN

retval :='JVR1' || AA || '000001';

ELSE

retval:='JVR' || BB;

NULL;

END IF;

RETURN retval;

END;

-- Accounts

-- ** JV

FUNCTION jvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(30);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM JV WHERE fin_year = p_fy AND unit_id = decode(p_unit,0,1,p_unit);

IF BB IS NULL THEN

retval := 'JVR-' || docid || '-1000001';

ELSE

retval:= 'JVR-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM JV WHERE fin_year = p_fy AND unit_id = decode(p_unit,0,1,p_unit);

IF BB IS NULL THEN

retval := 'JVR-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'JVR-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION jvno_hired (p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(30);

BEGIN

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM JV_hire WHERE fin_year = p_fy AND unit_id = decode(p_unit,0,1,p_unit);

IF BB IS NULL THEN

retval := 'JVH-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'JVH-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION crvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM CRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'CRV-' || docid || '-1000001';

ELSE

retval:= 'CRV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM CRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'CRV-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'CRV-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION tmp_crvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM TMP_CRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'CRV-TMP-' || docid || '-1000001';

ELSE

retval:= 'CRV-TMP-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM TMP_CRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'CRV-TMP-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'CRV-TMP-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION drvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM DRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DRV-' || docid || '-1000001';

ELSE

retval:= 'DRV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM DRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DRV-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'DRV-' || docid ||'-'||SFY||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION tmp_drvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM tmp_DRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DRV-TMP-' || docid || '-1000001';

ELSE

retval:= 'DRV-TMP-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM tmp_DRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DRV-TMP-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'DRV-TMP-' || docid ||'-'||SFY||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION dpvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM DPV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DPV-' || docid || '-1000001';

ELSE

retval:= 'DPV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM DPV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'DPV-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'DPV-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION pjvvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM PUR_M WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'PJV-' || docid || '-1000001';

ELSE

retval:= 'PJV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM PUR_M WHERE fin_year = p_fy AND unit_id = p_unit AND SUBSTR(1,3) ='PJV' ;

IF BB IS NULL THEN

retval := 'PJV-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'PJV-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION IMP(p_unit NUMBER, p_fy VARCHAR2 , p_type varchar2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM PUR_M WHERE fin_year = p_fy AND unit_id = p_unit and pjv_type = p_type AND SUBSTR(vno,1,3) ='IMP';

IF BB IS NULL THEN

retval := 'IMP-'||p_type||'-' || docid ||'-'||SFY|| '-1000001';

ELSE

retval:= 'IMP-'||p_type||'-' || docid ||'-'||SFY|| '-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION pjvvno_Type(p_unit NUMBER, p_fy VARCHAR2 , p_type varchar2) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM PUR_M WHERE fin_year = p_fy AND unit_id = p_unit and pjv_type = p_type;

IF BB IS NULL THEN

retval := 'PJV-'||p_type||'-' || docid || '-1000001';

ELSE

retval:= 'PJV-'||p_type||'-' || docid || '-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM PUR_M WHERE fin_year = p_fy AND unit_id = p_unit and pjv_type = p_type;

IF BB IS NULL THEN

retval := 'PJV-'||p_type||'-' || docid ||'-'||SFY|| '-1000001';

ELSE

retval:= 'PJV-'||p_type||'-' || docid ||'-'||SFY|| '-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION bpvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM BPV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BPV-' || docid || '-1000001';

ELSE

retval:= 'BPV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM BPV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BPV-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'BPV-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

end if;

RETURN retval;

END;

FUNCTION brvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM BPV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BRV-' || docid || '-1000001';

ELSE

retval:= 'BRV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM BRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BRV-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'BRV-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

end if;

RETURN retval;

END;

FUNCTION cash_bpvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM CBPV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'CBP-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'CBP-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION cash_brvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM CBRV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'CBR-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'CBR-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION btvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM BTV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BTV-' || docid || '-1000001';

ELSE

retval:= 'BTV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM BTV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BTV-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'BTV-' || docid ||'-'||sfy|| '-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION onlno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM LEDD WHERE fin_year = p_fy AND unit_id = p_unit

AND SUBSTR(VNO,1,7)='ONL-'||docid;

IF BB IS NULL THEN

retval := 'ONL-' || docid || '-1000001';

ELSE

retval:= 'ONL-' || docid ||'-'|| BB;

END IF;

ELSE

/*SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM LEDD WHERE fin_year = p_fy AND unit_id = p_unit

AND SUBSTR(VNO,1,7)='ONL-'||docid;

select max(substr(POSTING_VNO,-7))+1 INTO BB from BANK_TRN_D

where unit_id = p_unit and POSTING_VNO is not null

and fin_year = p_fy;

IF BB IS NULL THEN

retval := 'ONL-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'ONL-' || docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION bxvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

IF p_fy = '2017-18-1' THEN

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM ONLV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BTV-' || docid || '-1000001';

ELSE

retval:= 'BTV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM ONLV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BTV-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'BTV-' || docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION tmp_bxvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,-7)+1) INTO BB

FROM TMP_BTV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'BPV-TMP-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'BPV-TMP-' || docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION ptvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy = '2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,9,14)+1) INTO BB

FROM PTV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'PTV-' || docid || '-1000001';

ELSE

retval:= 'PTV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(VNO,16,14)+1) INTO BB

FROM PTV WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'PTV-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'PTV-' || docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION ordno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

cnt number;

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

if p_unit = 3700 then

docid := 'FJI';

SELECT MAX(SUBSTR(ORDNO,-7)+1) INTO BB

FROM ordm WHERE fin_year = p_fy AND unit_id = 37 and do_type = 3700 ;

else

select count(*) into cnt from smf_op_units WHERE op_unit_id = p_unit;

if nvl(Cnt,0) <> 0 then

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

else

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

end if;

SELECT MAX(SUBSTR(ORDNO,-7)+1) INTO BB

FROM ordm WHERE fin_year = p_fy AND unit_id = p_unit and SOLE_CITY_CODE is null

and nvl(do_type,0) <> 3700 ;

end if;

IF BB IS NULL THEN

retval := 'ORD-' || docid || '-'||sfy|| '-1000001';

ELSE

retval:= 'ORD-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION ordnoE(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(ORDNO,9,14)+1) INTO BB

FROM ordm WHERE fin_year = p_fy AND unit_id = p_unit and ORDER_TYPE ='EXPORT' and SOLE_CITY_CODE is null ;

IF BB IS NULL THEN

retval := 'ORD-' || 'HTE' || '-1000001';

ELSE

retval:= 'ORD-' || 'HTE' ||'-'|| BB;

END IF;

ELSE

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit;

SELECT MAX(SUBSTR(ORDNO,16,14)+1) INTO BB

FROM ordm WHERE fin_year = p_fy AND unit_id = p_unit and ORDER_TYPE ='EXPORT' and SOLE_CITY_CODE is null ;

IF BB IS NULL THEN

retval := 'ORD-' || 'HTE' ||'-'||SFY|| '-1000001';

ELSE

retval:= 'ORD-' || 'HTE' ||'-'||SFY||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION ordnoD(p_unit NUMBER, p_fy VARCHAR2, P_CITY Varchar2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

cnt number;

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

select count(*) into cnt from smf_units WHERE unit_id = p_unit;

if nvl(Cnt,0) <> 0 then

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

else

docid := 'XXX' ;

end if;

SELECT MAX(SUBSTR(ORDNO,-7)+1) INTO BB FROM ordm WHERE fin_year = p_fy AND unit_id = p_unit

and SOLE_CITY_CODE = P_CITY ;

IF BB IS NULL THEN

retval := 'ORD-' || docid || '-'||P_CITY||'-'|| sfy|| '-1000001';

ELSE

retval:= 'ORD-' || docid || '-'||P_CITY||'-'|| sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION tmp_ordnoD(p_unit NUMBER, p_fy VARCHAR2, P_CITY Varchar2 ) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

cnt number;

sfy varchar(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

select count(*) into cnt from smf_units WHERE unit_id = p_unit;

if nvl(Cnt,0) <> 0 then

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

else

docid := 'XXX' ;

end if;

SELECT MAX(SUBSTR(ORDNO,-7)+1) INTO BB FROM tmp_ordm WHERE fin_year = p_fy AND unit_id = p_unit

and SOLE_CITY_CODE = P_CITY ;

IF BB IS NULL THEN

retval := 'ORD-' || docid || '-'||P_CITY||'-'|| sfy|| '-1000001';

ELSE

retval:= 'ORD-' || docid || '-'||P_CITY||'-'|| sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION sivno(p_unit NUMBER, p_fy VARCHAR2 , p_mtl_store varchar2 := 0 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(30);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_OGP_M WHERE fin_year = p_fy AND unit_id = p_unit and mtl_store_id = p_mtl_store

and substr(SI_no,1,3) ='SIV';

IF BB IS NULL THEN

retval := 'SIV-' || p_mtl_store||'-'|| docid || '-1000001';

ELSE

retval:= 'SIV-' || p_mtl_store||'-'|| docid ||'-'|| BB;

END IF;

ELSE

IF P_UNIT = 37 THEN

if p_mtl_store > 100 then

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_OGP_M WHERE fin_year = p_fy AND unit_id = 37 and mtl_store_id = p_mtl_store

and substr(SI_no,1,11) ='SIV-'||p_mtl_store||'-PST';

Else

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_OGP_M WHERE fin_year = p_fy /*AND unit_id = 37*/

and mtl_store_id = p_mtl_store

and substr(SI_no,1,10) ='SIV-13-PST';

End if;

/* SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_OGP_M WHERE fin_year = p_fy AND unit_id = p_unit and mtl_store_id = p_mtl_store

and substr(SI_no,1,3) ='SIV';

ELSE

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_OGP_M WHERE fin_year = p_fy AND unit_id = p_unit and mtl_store_id = p_mtl_store

and substr(SI_no,1,3) ='SIV';

END IF;

IF BB IS NULL THEN

retval := 'SIV-' || p_mtl_store||'-'|| docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'SIV-' || p_mtl_store||'-'|| docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION srvno(p_unit NUMBER, p_fy VARCHAR2 , p_mtl_store varchar2 := 0) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

oid number;

sfy VARCHAR2(30);

BEGIN

select organizationid into oid from smf_units where unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF P_FY = '2017-18-1' THEN

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_PTN_M WHERE fin_year = p_fy AND unit_id = p_unit

and substr(SI_no,1,3) ='SRV';

IF BB IS NULL THEN

retval := 'SRV' || '-'|| docid || '-1000001';

ELSE

retval:= 'SRV' || '-'|| docid ||'-'|| BB;

END IF;

ELSE

--SELECT distinct DOC_ID INTO docid FROM smf_units WHERE decode(unit_id,7,7,27,7,37,7,unit_id) = decode(p_unit,7,7,27,7,37,7,p_unit);

SELECT distinct DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

if oid =2 then

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_PTN_M WHERE fin_year = p_fy

AND organization_id = oid

and substr(SI_no,1,3) ='SRV';

else

SELECT MAX(SUBSTR(SI_NO,-7)+1) INTO BB

FROM WH_PTN_M WHERE fin_year = p_fy

AND unit_id = p_unit and substr(SI_no,1,3) ='SRV';

end if;

IF BB IS NULL THEN

retval := 'SRV' || '-'|| docid || '-' || sfy|| '-1000001';

ELSE

retval:= 'SRV' || '-'|| docid ||'-'||sfy||'-'|| BB;

END IF;

END IF ;

RETURN retval;

END;

FUNCTION srtnvno(p_unit NUMBER, p_fy VARCHAR2) RETURN VARCHAR2 as

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3) ;

oid number;

sfy VARCHAR2(30);

BEGIN

select organizationid into oid from smf_units where unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT distinct DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

SELECT MAX(SUBSTR(SR_NO,-7)+1) INTO BB FROM INV_SR_M WHERE fin_year = p_fy AND unit_id = p_unit ;

IF BB IS NULL THEN

retval := 'SRV' || '-'|| docid || '-' || sfy|| '-1000001';

ELSE

retval:= 'SRV' || '-'|| docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION slvno(p_unit NUMBER, p_fy VARCHAR2 , p_loc number := 0 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

locid number;

sfy VARCHAR2(30);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO docid FROM smf_op_units WHERE op_unit_id = p_unit ; --name_of.op_unit_from_unit(p_unit);

if p_loc = 43 then

SELECT lpad(MAX(SUBSTR(SI_NO,-7)+1),'7','0') INTO BB

FROM INV_OGP_M

WHERE fin_year = p_fy AND unit_id = p_unit and si_no is not null

and ogp_location = 43

and substr(si_no , 1,3) ='SLV';

docid := 'GBK';

else

SELECT lpad(MAX(SUBSTR(SI_NO,-7)+1),'7','0') INTO BB

FROM INV_OGP_M

WHERE fin_year = p_fy AND unit_id = p_unit and si_no is not null

and ogp_location = p_loc

and substr(si_no , 1,3) ='SLV';

end if;

IF P_FY = '2017-18-1' THEN

IF BB IS NULL THEN

retval := 'SLV-' || docid || '-1000001';

ELSE

retval:= 'SLV-' || docid ||'-'|| BB;

END IF;

ELSE

IF BB IS NULL THEN

retval := 'SLV-' || docid ||'-'||sfy ||'-'||p_loc|| '-1000001';

ELSE

retval:= 'SLV-' || docid ||'-'||sfy ||'-'||p_loc||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION svrno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BEGIN

NULL;

RETURN NULL;

END;

FUNCTION ssvno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(30);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(SSV_NO,9,14)+1) INTO BB

FROM INV_OGP_M WHERE fin_year = p_fy AND unit_id = p_unit and substr(ssv_no,1,3) ='SSV';

IF BB IS NULL THEN

retval := 'SSV-' || docid || '-1000001';

ELSE

retval:= 'SSV-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(SSV_NO,16,14)+1) INTO BB

FROM INV_OGP_M WHERE fin_year = p_fy AND unit_id = p_unit and substr(ssv_no,1,3) ='SSV';

IF BB IS NULL THEN

retval := 'SSV-' || docid ||'-'||sfy|| '-1000001';

ELSE

retval:= 'SSV-' || docid ||'-'||sfy||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION siuno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

BB NUMBER;

retval VARCHAR2(35);

docid VARCHAR2(3);

sfy VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO docid FROM smf_units WHERE unit_id = p_unit;

sfy := Date_Of.Sfy_from_fy(p_fy);

IF p_fy ='2017-18-1' THEN

SELECT MAX(SUBSTR(invno,9,14)+1) INTO BB

FROM INT_SAL_M WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'SIU-' || docid || '-1000001';

ELSE

retval:= 'SIU-' || docid ||'-'|| BB;

END IF;

ELSE

SELECT MAX(SUBSTR(invno,16,14)+1) INTO BB

FROM INT_SAL_M WHERE fin_year = p_fy AND unit_id = p_unit;

IF BB IS NULL THEN

retval := 'SIU-' || docid ||'-'||sfy || '-1000001';

ELSE

retval:= 'SIU-' || docid ||'-'||sfy ||'-'|| BB;

END IF;

RETURN retval;

END;

FUNCTION pslipno(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

retval varchar2(35);

docid varchar2(4) := 'SLP';

cnt number;

cntr varchar2(30);

did varchar2(10);

sfy VARCHAR2(30);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO DID FROM smf_units WHERE unit_id = p_unit;

IF P_FY ='2017-18-1' THEN

SELECT NVL(MAX(SUBSTR(VNO,-6)),0)+1 INTO cnt FROM PAYMENT_SLIP WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||DID||'-'||cntr;

ELSE

SELECT NVL(MAX(SUBSTR(VNO,-6)),0)+1 INTO cnt FROM PAYMENT_SLIP WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||SFY||'-'||DID||'-'||cntr;

END IF;

return retval;

end;

FUNCTION Wh_Order(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'ORD';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

-- SELECT NVL(MAX(SUBSTR(ORDER_NO,-6)),0)+1 INTO cnt FROM WH_ORDER_M WHERE unit_id = p_unit AND fin_year = P_FY AND MTL_STORE_ID = P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||P_FY||'-' ||P_SBID||'-'||cntr;

END;

FUNCTION WHRTN(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'RPT';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(pro_NO,-6)),0)+1 INTO cnt FROM WH_RTN_M WHERE unit_id = p_unit AND fin_year = P_FY AND user_id = P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||P_SBID||'-'||cntr;

END;

FUNCTION WHPTN(P_UNIT NUMBER, P_SID NUMBER := '0', P_SBID NUMBER := '0' , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'RTN';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

retval varchar(40);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(PTN_NO,-6)),0)+1 INTO cnt

FROM WH_PTN_M WHERE unit_id = p_unit

AND fin_year = P_FY AND MTL_STORE_ID = P_SBID ; -- last max number

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||uid||'-'||sfy||'-' ||P_SBID||'-'||cntr;

return retval;

END;

FUNCTION pocontract(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'CNT';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(CNT_NO,-6)),0)+1 INTO cnt FROM SMF_PO_CONTRACT WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION FORECAST(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'SFC';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(SAL_NO,-6)),0)+1 INTO cnt FROM SAL_FORCASTE_M WHERE sal_unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION REJECTION(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'RJV';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(invoice_NO,-6)),0)+1 INTO cnt FROM INV_PTN_M WHERE unit_id = p_unit AND fin_year = P_FY AND PTN_TYPE ='Rejection' ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PKG_COST(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'CST';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(COST_NO,-6)),0)+1 INTO cnt FROM BOM_PACKAGES_COST WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PKG_COST_EXP(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'CST';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(COST_NO,-6)),0)+1 INTO cnt FROM COST_SHEET_PKG_EXP WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

--- PO

FUNCTION PO_REQ(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'REQ';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(REQ_NO,-9)),0)+1 INTO cnt FROM SMF_PO_REQ_M WHERE unit_id = p_unit ;-- AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,9,'0');

RETURN UID||cntr;

END;

FUNCTION PO_NO(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PO';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(REQ_NO,-9)),0)+1 INTO cnt FROM SMF_PO_REQ_M WHERE unit_id = p_unit; -- AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,9,'0');

RETURN UID||cntr;

END;

FUNCTION PO_NO (P_UNIT NUMBER , P_SID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid VARCHAR(3) := 'PO';

UID CHAR(3) ;

fy VARCHAR(7);

cnt NUMBER;

retval VARCHAR2(30);

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(PONO,-6)),0)+1 INTO cnt FROM PO_M WHERE unit_id = p_unit AND fin_year = P_FY AND STORE_ID = P_SID;

DBMS_OUTPUT.PUT_LINE(DOCID);

cntr :=LPAD(cnt,6,'0');

retval := docid||'-'||UID||'-'||lpad(p_sid,2,'0')||'-'||sfy|| '-'|| cntr;

RETURN retval;

END;

FUNCTION BOM_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'BOM';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(BOM_NO,-7)),0)+1 INTO cnt FROM BOM_PACKAGES_M WHERE unit_id = p_unit ; --AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION BOM_ORDER_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'ORD';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(ORDER_NO,-7)),0)+1 INTO cnt FROM PO_PACKAGES_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION BOM_JOB_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'JOB';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(JOB_NO,-7)),0)+1 INTO cnt FROM BOM_JOB_CARD_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION BOM_LAY_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'LAY';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(LAY_NO,-7)),0)+1 INTO cnt FROM BOM_JOB_LAYOUT_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION BOM_PLAN_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PPC';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

--select doc_year into fy from CALENDAR where ndate = to_char(trunc(time_of.date_gate(sysdate)),'rrrrmmdd'); -- financial year

SELECT NVL(MAX(SUBSTR(PLAN_NO,-7)),0)+1 INTO cnt FROM BOM_LAYOUT_PLAN_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PDS_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PDS';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(BOM_NO,-7)),0)+1 INTO cnt FROM BOM_PDS_M WHERE unit_id = p_unit ;--AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PJF_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'PJF';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(bilty_vno,-7)),0)+1 INTO cnt FROM WH_OGP_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION FTV_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'FTV';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(vno,-7)),0)+1 INTO cnt FROM FTV WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION CONX_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'CON';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(CNT_NO,-7)),0)+1 INTO cnt FROM INV_CNT_M WHERE unit_id = p_unit AND fin_year = fy; -- last max number

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION WH_AUDIT_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'AUD';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(AUD_NO,-7)),0)+1 INTO cnt FROM WH_AUD_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION LOCX_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'LOC';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(TRN_NO,-7)),0)+1 INTO cnt FROM INV_LOCATION_TRN_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION FGS_REQ_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'REQ';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(REQ_NO,-7)),0)+1 INTO cnt FROM FGS_REQ_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION ticket_no(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'TKT';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(tkt_no,-7)),0)+1 INTO cnt FROM TKT_DETAIL

WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION WLOCX_NO(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS -- WAREHOUSE LOCATIONS

docid CHAR(3) := 'COL';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(TRN_NO,-7)),0)+1 INTO cnt FROM WH_LOCATION_TRN_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION ISS_TO_PROD(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'I2P';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(ISS_NO,-7)),0)+1 INTO cnt FROM WH_ISS_PROD_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION BOX_SALEORDER(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as -- ISSUE TO PRODUCTION

docid CHAR(3) := 'ISO';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(ISO_NO,-7)),0)+1 INTO cnt FROM INTER_SO_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION BOX_JOBCARD(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'JBO';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(job_NO,-7)),0)+1 INTO cnt FROM INTER_JOB_CARD WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION DISPATCHPLAN(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'JBO';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PLAN_NO,-7)),0)+1 INTO cnt FROM WH_DISPATCH_PLAN_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION postages(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'PST';

UID CHAR(3) ;

fy VARCHAR(10) := p_fy;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(POST_NO,-6)),0)+1 INTO cnt FROM POSTAGE_M WHERE unit_id = p_unit AND fin_year = fy;

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION token(P_UNIT NUMBER, p_date date ) RETURN VARCHAR2 as

docid CHAR(3) := 'TKN';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(token_NO,-6)),0)+1 INTO cnt FROM WH_VEHICLE_TOKEN WHERE unit_id = p_unit and trunc(token_date) = trunc(p_date) ;

cntr :=LPAD(cnt,6,'0');

RETURN UID||'-' ||to_char(p_date,'YYMMDD') ||'-' ||cntr;

END;

FUNCTION QOT(P_UNIT NUMBER ) RETURN VARCHAR2 as

docid CHAR(3) := 'QOT';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(QOT_NO,-7)),0)+1 INTO cnt FROM PO_QUOTE_M WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||cntr;

END;

FUNCTION WOD(P_UNIT NUMBER) RETURN VARCHAR2 as

docid CHAR(3) := 'WOD';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(WOD_NO,-7)),0)+1 INTO cnt FROM SMF_WORK_ORDER_M WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||cntr;

END;

FUNCTION MOD_CIV(P_UNIT NUMBER) RETURN VARCHAR2 as

docid CHAR(3) := 'CMO';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(MOVE_NO,-7)),0)+1 INTO cnt FROM CIV_MOVE_ORDER WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||cntr;

END;

FUNCTION WOD_CIV(P_UNIT NUMBER) RETURN VARCHAR2 as

docid CHAR(3) := 'CWO';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(to_number(SUBSTR(WOD_NO,-7))),0)+1 INTO cnt FROM CIV_WORK_ORDER_M WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||cntr;

END;

FUNCTION FLT_BILL(P_UNIT NUMBER) RETURN VARCHAR2 as

docid CHAR(3) := 'FLT';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT nvl(max(to_number(substr(BILL_NO,-7))),0)+1 INTO cnt FROM FLT_BILL_M WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||cntr;

END;

FUNCTION vbook(P_UNIT NUMBER ) RETURN VARCHAR2 as

docid CHAR(3) := 'TKN';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(BOOKING_NO,-7)),0)+1 INTO cnt FROM WH_VEHICLE_BOOKING WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN UID||'-' ||cntr;

END;

FUNCTION LABORBREAK(P_UNIT NUMBER , P_FY VARCHAR2) RETURN VARCHAR2 AS

docid CHAR(3) := 'BIL';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PLAN_NO,-7)),0)+1 INTO cnt FROM BOM_LAYOUT_BRK WHERE unit_id = p_unit AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PROD_PKG(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'MPD';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PROD_NO,-7)),0)+1 INTO cnt FROM BOM_PRODUCTION_M WHERE unit_id = p_unit AND PRODUCTION_TYPE='OFFSET' AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||P_UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PKGS_DIE(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'MDC';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PROD_NO,-7)),0)+1 INTO cnt FROM BOM_PRODUCTION_M WHERE unit_id = p_unit AND PRODUCTION_TYPE='DIE' AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||P_UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PKGS_FG(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'FGS';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PROD_NO,-7)),0)+1 INTO cnt FROM BOM_PRODUCTION_M WHERE unit_id = p_unit AND PRODUCTION_TYPE='PASTE' AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||P_UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PKGS_WP(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'WNP';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PROD_NO,-7)),0)+1 INTO cnt FROM BOM_PRODUCTION_M WHERE unit_id = p_unit AND PRODUCTION_TYPE='WP' AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||P_UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PKGS_LAM(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'LAM';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PROD_NO,-7)),0)+1 INTO cnt FROM BOM_PRODUCTION_M WHERE unit_id = p_unit AND PRODUCTION_TYPE='LAM' AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||P_UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION RDR(P_UNIT NUMBER, P_UID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'RDR';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(DMD_NO,-6)),0)+1 INTO cnt FROM inv_rdmd_m WHERE unit_id = p_unit and user_id = p_uid AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||P_UID||'-'||Sfy||'-'||cntr;

END;

function downtine(p_unit number , p_fy varchar2) return varchar2 as

docid CHAR(3) := 'BIL';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(PROD_NO,-7)),0)+1 INTO cnt FROM BOM_PRODUCTION_DOWN_M WHERE unit_id = p_unit ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION RRJ(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PRJ';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(INS_NO,-7)),0)+1 INTO cnt FROM PROJECT_EXPENSE_M WHERE unit_id = p_unit AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION PROD_SEMI(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PRJ';

UID CHAR(3) ;

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit;

SELECT NVL(MAX(SUBSTR(pro_NO,-7)),0)+1 INTO cnt FROM INV_PRODUCTION_m WHERE unit_id = p_unit AND fin_year = P_FY ;

cntr :=LPAD(cnt,7,'0');

RETURN docid||'-'||UID||'-'||Sfy||'-'||cntr;

END;

FUNCTION ADV_FORECAST(p_unit NUMBER, p_fy VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'ASF';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(SAL_NO,-6)),0)+1 INTO cnt FROM SAL_ADV_FORCASTE_M WHERE sal_unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION counter(p_unit NUMBER, table_name VARCHAR2 ) RETURN VARCHAR2 as

retval varchar2(400);

strr varchar2(400);

uids varchar2(10);

cntr varchar2(10);

begin

SELECT DOC_ID INTO UIDS FROM smf_units WHERE unit_id = p_unit; -- document id

IF p_unit IS NOT NULL THEN

strr := 'SELECT NVL(COUNT(*),0)+1 FROM '|| table_name ||' WHERE unit_id = '|| p_unit;

EXECUTE IMMEDIATE strr INTO RETVAL;

cntr :=LPAD(RETVAL,6,'0');

RETURN UIDS||'-' ||cntr;

ELSE

RETVAL := '';

END IF;

return retval;

end;

FUNCTION counter(p_unit NUMBER, table_name VARCHAR2, P_DOC varchar2 ) RETURN VARCHAR2 as

retval varchar2(400);

strr varchar2(400);

uids varchar2(10);

cntr varchar2(10);

begin

SELECT DOC_ID INTO UIDS FROM smf_units WHERE unit_id = p_unit; -- document id

IF p_unit IS NOT NULL THEN

strr := 'SELECT NVL(COUNT(*),0)+1 FROM '|| table_name ||' WHERE unit_id = '|| p_unit;

EXECUTE IMMEDIATE strr INTO RETVAL;

cntr :=LPAD(RETVAL,6,'0');

RETURN P_DOC||'-'||UIDS||'-' ||cntr;

ELSE

RETVAL := '';

END IF;

return retval;

end;

FUNCTION col_counter(p_unit NUMBER, table_name VARCHAR2, p_col varchar2 ) RETURN VARCHAR2 as

retval varchar2(40);

strr varchar2(400);

uids varchar2(10);

cntr varchar2(10);

begin

SELECT DOC_ID INTO UIDS FROM smf_units WHERE unit_id = p_unit; -- document id

IF p_unit IS NOT NULL THEN

strr := 'SELECT NVL(COUNT(*),0)+1 FROM '|| table_name ||' WHERE unit_id = '|| p_unit || ' and ' || p_col || ' is not null ' ;

dbms_output.put_line(strr);

EXECUTE IMMEDIATE strr INTO RETVAL;

cntr :=LPAD(RETVAL,6,'0');

RETURN UIDS||'-' ||cntr;

ELSE

RETVAL := '';

END IF;

return retval;

end;

FUNCTION QA_PAPER(P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'ASF';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(INS_NO,-6)),0)+1 INTO cnt FROM INV_PB_Q_INSPECTION_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION QC_PROD (P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'ASF';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(QC_NO,-6)),0)+1 INTO cnt FROM QA_INSPECTION_DB_M WHERE unit_id = p_unit ;-- AND fin_year = P_FY ;

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION CPH_REJ (P_UNIT NUMBER, P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'RJX';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(REJ_NO,-6)),0)+1 INTO cnt FROM INV_TRD_REJ_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PL_CLOSE (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PLC';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(POST_NO,-6)),0)+1 INTO cnt FROM PL_MONTH_POSTING_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PKG_ISO (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'ISO';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(PKG_ISO_NO,-6)),0)+1 INTO cnt FROM PO_PACKAGES_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PROD_TXT (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PGT';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(TGT_NO,-6)),0)+1 INTO cnt FROM PROD_TARGET_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PAY_REQUEST (P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'PRQ';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ATTRIBUTE2,-6)),0)+1 INTO cnt FROM LEDD WHERE unit_id = p_unit AND ATTRIBUTE1 ='Y';

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

function task_no (p_unit number, p_dated date) return varchar2 as

docid char(4) := 'TASK';

uid char(3) ;

cnt number;

cntr varchar2(10);

sfy varchar2(20);

begin

sfy := to_char(to_date(p_dated),'RRRR');

select doc_id into uid from smf_units where unit_id = p_unit;

select nvl(max(substr(task_no,-4)),0)+1 into cnt from smf_daily_tasks_master where unit_id = p_unit ;

cntr :=lpad(cnt,4,'0');

return docid||'-'||uid||'-'||sfy||'-' ||cntr;

end;

function QRCV (P_UNIT NUMBER , P_SID NUMBER := '0', P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid char(3) := 'RCV';

uid char(3) ;

cnt number;

cntr varchar2(10);

sfy varchar2(20);

begin

sfy := Date_Of.Sfy_from_fy(p_fy);

select doc_id into uid from smf_units where unit_id = p_unit;

select nvl(max(substr(RCV_NO,-6)),0)+1 into cnt from FLOOR_RCV_M where unit_id = p_unit and mtl_Store_id = p_sid;

cntr :=lpad(cnt,6,'0');

return docid||'-'||uid||'-'||sfy||'-'|| lpad(p_sid,3,0)||'-'||cntr;

end;

FUNCTION FLR_PLAN(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'FLN';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(PLAN_NO,-6)),0)+1 INTO cnt FROM PROD_PLAN_FLM_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION FLR_ISS(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'SIL';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISS_NO,-6)),0)+1 INTO cnt FROM WH_BATCH_ISSUE_M WHERE unit_id = p_unit ; --AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION PRP(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'PRP';

UID CHAR(3) ;

fy VARCHAR(7);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(REQ_NO,-6)),0)+1 INTO cnt FROM SMF_PO_REQ_PLAN_M WHERE unit_id = p_unit ; --AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION HSE_IA(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'IAL';

UID CHAR(3) ;

fy VARCHAR(1);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(IAL_NO,-6)),0)+1 INTO cnt FROM HSE_IALOG WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION HSE_MDR(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 AS

docid CHAR(3) := 'MDR';

UID CHAR(3) ;

fy VARCHAR(1);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(DIS_NO,-6)),0)+1 INTO cnt FROM HSE_DISP_REG_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

FUNCTION HSE_ISD(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 as

docid CHAR(3) := 'ISD';

UID CHAR(3) ;

fy VARCHAR(1);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(ISD_NO,-6)),0)+1 INTO cnt FROM hse_disp_iss_m WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

-- WORK PERMIT

FUNCTION HSE_WP(P_UNIT NUMBER , P_FY VARCHAR2 ) RETURN VARCHAR2 As

docid CHAR(3) := 'WPT';

UID CHAR(3) ;

fy VARCHAR(1);

nfy VARCHAR(5);

cnt NUMBER;

cntr VARCHAR2(10);

sfy VARCHAR2(20);

BEGIN

sfy := Date_Of.Sfy_from_fy(p_fy);

SELECT DOC_ID INTO UID FROM smf_units WHERE unit_id = p_unit; -- document id

SELECT NVL(MAX(SUBSTR(WP_NO,-6)),0)+1 INTO cnt FROM HSE_WORK_PERMIT_M WHERE unit_id = p_unit AND fin_year = P_FY ; -- last max number

cntr :=LPAD(cnt,6,'0');

RETURN docid||'-'||UID||'-'||sfy||'-' ||cntr;

END;

Kamran Shahzad

Monday, 29 April 2024

Ledger Restrict Function

Date of Pkg

Document OF PKG

Check Internet Connectivity on Local System

Report Abuse