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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
END;
/
No comments:
Post a Comment