A2_SPSF.SQL

CREATE OR REPLACE PROCEDURE A2AddSku(pskuid number, pskuname varchar2) AS
BEGIN
INSERT INTO A2SKU VALUES (pskuid,pskuname,0);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001, 'Add Sku failed. Sku already exists ' || pskuid);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20999, SQLERRM);
END;
/
CREATE OR REPLACE PROCEDURE A2AddLoc(plocid number, plocname varchar2) AS
BEGIN
INSERT INTO A2LOC VALUES (plocid,plocname,0,999);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20002, 'Add Loc failed. Loc already exists ' || plocid);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20999, SQLERRM);
END;
/
CREATE OR REPLACE PROCEDURE A2RESET AS
BEGIN
DELETE FROM A2SKU;
DELETE FROM A2LOC;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
CREATE OR REPLACE FUNCTION A2ListAllSkus RETURN VARCHAR2 AS
vReturnData VARCHAR2(2000);
sku_rec A2SKU%ROWTYPE;
CURSOR SKU_CUR IS SELECT SKUID,SKUNAME,SKUQTY FROM A2SKU;
BEGIN
vReturnData := 'SKU ID        NAME             QUANTITY '||chr(10);
vReturnData := vReturnData || '========================================'||chr(10);
OPEN SKU_CUR;
LOOP
	FETCH SKU_CUR INTO sku_rec;
	EXIT WHEN SKU_CUR%NOTFOUND;
	vReturnData := vReturnData || sku_rec.SKUID || '        ' || sku_rec.SKUNAME || '             ' || sku_rec.SKUQTY || chr(10);
END LOOP;
CLOSE SKU_CUR;	
RETURN vreturndata;
END;
/
CREATE OR REPLACE FUNCTION A2ListAllLocs RETURN VARCHAR2 AS
vReturnData VARCHAR2(2000);
Loc_rec A2LOC%ROWTYPE;
CURSOR LOC_CUR IS SELECT LOCID,LOCNAME,LOCQTY,MAXQTY FROM A2LOC;
BEGIN
vReturnData := 'LOC ID        NAME             QUANTITY '||chr(10);
vReturnData := vReturnData || '========================================'||chr(10);
OPEN LOC_CUR;
LOOP
	FETCH LOC_CUR INTO Loc_rec;
	EXIT WHEN LOC_CUR%NOTFOUND;
	vReturnData := vReturnData || Loc_rec.LOCID || '        ' || Loc_rec.LOCNAME || '             ' || Loc_rec.LOCQTY || chr(10);
END LOOP;
CLOSE LOC_CUR;	
RETURN vreturndata;
END;
/
CREATE OR REPLACE FUNCTION A2CountSkus RETURN NUMBER AS
vReturnNumber NUMBER;
BEGIN
SELECT COUNT(SKUID) INTO vReturnNumber FROM A2SKU;
RETURN vReturnNumber;
END;
/
CREATE OR REPLACE FUNCTION A2CountLocs RETURN NUMBER AS
vReturnNumber NUMBER;
BEGIN
SELECT COUNT(LOCID) INTO vReturnNumber FROM A2LOC;
RETURN vReturnNumber;
END;
/

CREATE OR REPLACE FUNCTION A2AddBox(pskuid number,plocid number) RETURN NUMBER AS
vReturnNumber NUMBER;
vBoxID NUMBER;
vCons_Name Varchar(100);
BEGIN
  vBoxID := A2BOXSEQ.nextval;
  INSERT INTO A2BOX VALUES (vBoxID,pskuid,plocid);
  vReturnNumber := vBoxID;
  RETURN vReturnNumber;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
  RAISE_APPLICATION_ERROR(-20003,'Add box failed. Boxid already exists '||vBoxID);
  RETURN vReturnNumber;
  WHEN OTHERS THEN
  vCons_Name := strip_constraint_name(SQLERRM);
  IF vCons_Name='FK_A2BOX_SKUID' THEN
  RAISE_APPLICATION_ERROR(-20004,'Add box failed. Unknown SKU ID: '||pskuid);
   RETURN vReturnNumber;
  ELSIF vCons_Name='FK_A2BOX_LOCID' THEN
  RAISE_APPLICATION_ERROR(-20004,'Add box failed. Unknown LOCID ID: '||plocid);
   RETURN vReturnNumber;
  ELSE
  RAISE;
   RETURN vReturnNumber;
  END IF;
END;
/
CREATE OR REPLACE PROCEDURE A2RESET AS
BEGIN
DELETE FROM A2Box;
DELETE FROM A2SKU;
DELETE FROM A2LOC;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
CREATE OR REPLACE FUNCTION A2ListAllBoxes RETURN VARCHAR2 AS
vReturnData VARCHAR2(2000);
vbox_cur A2Box%ROWTYPE;
CURSOR BOX_CUR IS SELECT BOXID,SKUID,LOCID FROM A2BOX;
BEGIN
vReturnData := 'List of All Boxes:' || chr(10);
vReturnData := vReturnData || 'BOX ID        SKU ID             LOC ID '||chr(10);
vReturnData := vReturnData || '========================================'||chr(10);
OPEN BOX_CUR;
LOOP
	FETCH BOX_CUR INTO vbox_cur;
	EXIT WHEN BOX_CUR%NOTFOUND;
	vReturnData := vReturnData || vbox_cur.BOXID || '        ' || vbox_cur.SKUID || '             ' || vbox_cur.LOCID || chr(10);
END LOOP;
CLOSE BOX_CUR;	
RETURN vReturnData;
EXCEPTION
WHEN OTHERS THEN
RAISE;
RETURN vReturnData;
END;
/
CREATE OR REPLACE FUNCTION A2ListOneLoc(plocid NUMBER) RETURN VARCHAR2 AS
vReturnData varchar2(2000);
vBoxSkuID A2Box%ROWTYPE;
CURSOR BOX_SKU_CUR IS SELECT BOXID,SKUID,LOCID FROM A2BOX WHERE LOCID=plocid;
BEGIN
vReturnData := 'LIST OF ALL BOXES FOR LOCATION ' || plocid || ':' ||chr(10);
vReturnData := vReturnData || 'BOX ID        SKU ID'||chr(10);
vReturnData := vReturnData || '===================='||chr(10);
OPEN BOX_SKU_CUR;
LOOP
	FETCH BOX_SKU_CUR INTO vBoxSkuID;
	EXIT WHEN BOX_SKU_CUR%NOTFOUND;
	vReturnData := vReturnData || vBoxSkuID.BOXID || '        ' || vBoxSkuID.SKUID || chr(10);
END LOOP;
CLOSE BOX_SKU_CUR;	
RETURN vReturnData;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20020,'ListOneLoc failed. Location Invalid: '||plocid);
RETURN vReturnData;
END;
/
CREATE OR REPLACE FUNCTION A2CountBoxes RETURN NUMBER AS
vReturnNumber NUMBER;
BEGIN
SELECT COUNT(BOXID) INTO vReturnNumber FROM A2BOX;
RETURN vReturnNumber;
END;
/
CREATE OR REPLACE PROCEDURE A2SKUQTYINC(pskuid NUMBER) AS
BEGIN
UPDATE A2SKU SET SKUQTY=SKUQTY+1 WHERE SKUID=pskuid;
IF(SQL%ROWCOUNT < 1) THEN
RAISE_APPLICATION_ERROR(-20006,'Sku Qty Increment failed. Unknown SKU ID: '||pskuid);
END IF;
END;
/
CREATE OR REPLACE PROCEDURE A2LOCQTYINC(plocid NUMBER) AS
BEGIN
UPDATE A2LOC SET LOCQTY=LOCQTY+1 WHERE LOCID=plocid;
IF(SQL%ROWCOUNT < 1) THEN
RAISE_APPLICATION_ERROR(-20007,'Loc Qty Increment failed. Unknown LOC ID: '||plocid);
END IF;
END;
/
CREATE OR REPLACE FUNCTION A2SumSkuQty RETURN NUMBER AS
vReturnNumber NUMBER;
BEGIN
SELECT SUM(SkuQty) INTO vReturnNumber FROM A2SKU;
RETURN vReturnNumber;
END;
/
CREATE OR REPLACE FUNCTION A2SumLocQty RETURN NUMBER AS
vReturnNumber NUMBER;
BEGIN
SELECT SUM(LocQty) INTO vReturnNumber FROM A2LOC;
RETURN vReturnNumber;
END;
/
CREATE OR REPLACE PROCEDURE A2SetMaxQty(plocid NUMBER, pqty NUMBER) AS
vLOCQTY NUMBER;
BEGIN
SELECT LOCQTY INTO vLOCQTY FROM A2LOC WHERE LOCID=plocid;
IF(vLOCQTY < pqty) THEN
UPDATE A2LOC SET MaxQty=pqty WHERE LOCID=plocid;
ELSE
RAISE_APPLICATION_ERROR(-20009,'Current Qty exceeds MaxQty');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20008,'Location does not exist: '||plocid);
WHEN OTHERS THEN
RAISE;
END;
/
CREATE OR REPLACE PROCEDURE A2MOVEBOX(pboxid NUMBER, pToLocId NUMBER) AS
vBoxID NUMBER;
vToLocID NUMBER;
vMaxQty NUMBER;
vLOCID NUMBER;
vCons_Name VARCHAR2(100);
e_ToLoc EXCEPTION;
BEGIN
SELECT LOCID INTO vLOCID FROM A2BOX WHERE BOXID=pboxid;
UPDATE A2BOX SET LOCID=pToLocId WHERE BOXID=pboxid;
UPDATE A2LOC SET LOCQTY = LOCQTY - 1 WHERE LOCID=vLOCID;
UPDATE A2LOC SET LOCQTY = LOCQTY + 1 WHERE LOCID=pToLocId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20010,'Move Box failed. Boxid does not exist: '||pboxid);
WHEN OTHERS THEN
vCons_Name := STRIP_CONSTRAINT_NAME(SQLERRM);
IF(vCons_Name = 'CC_LOC_QTY_OVERFLOW') THEN
RAISE_APPLICATION_ERROR(-20012,'Move Box failed. Destination location has reached maximum capacity.: '||pToLocId);
ELSIF(vCons_Name = 'FK_A2BOX_LOCID') THEN
RAISE_APPLICATION_ERROR(-20011,'Move Box failed. To location does not exist: '||pToLocId);
ELSE
RAISE;
END IF;
END;
/
CREATE OR REPLACE PROCEDURE A2DeleteBox(pboxid NUMBER) AS
vBoxID NUMBER;
vLocID NUMBER;
vSKUID NUMBER;
BEGIN
SELECT LOCID,SKUID INTO vLocID,vSKUID FROM A2BOX WHERE BOXID=pboxid;
DELETE FROM A2BOX WHERE BOXID=pboxid;
UPDATE A2LOC SET LOCQTY = LOCQTY - 1 WHERE LOCID=vLocID;
UPDATE A2SKU SET SKUQTY = SKUQTY - 1 WHERE SKUID=vSKUID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20013,'Delete Box failed. Boxid does not exist: '||pboxid);
WHEN OTHERS THEN
RAISE;
END;
/
CREATE OR REPLACE PROCEDURE A2DeleteSKU(pskuid NUMBER) AS
vBoxID NUMBER;
vLocID NUMBER;
vSKUID NUMBER;
vc_BoxID SYS_REFCURSOR;
BEGIN
SELECT SKUID INTO vSKUID FROM A2SKU WHERE SKUID=pskuid;
OPEN vc_BoxID FOR SELECT BOXID FROM A2BOX WHERE skuid=pskuid;
LOOP
FETCH vc_BoxID INTO vBOXID;
EXIT WHEN vc_BoxID%NOTFOUND;
SELECT LOCID INTO vLocID FROM A2BOX WHERE boxid = vBOXID;
UPDATE A2LOC SET LOCQTY = LOCQTY - 1 WHERE LOCID = vLocID;
DELETE FROM A2BOX WHERE BOXID = vBOXID;
END LOOP;
DELETE FROM A2SKU WHERE SKUID=pskuid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20014,'Delete SKU failed. skuid does not exist: '||pskuid);
WHEN OTHERS THEN
RAISE;
END;
/