sil_deficiencies

gistfile1.txt

/*-----Initial Tests-------------------------*/
DECLARE E1 CURSOR FOR
SELECT SERIAL.SERIAL,SERIALNAME
FROM SERIAL,SERIALA
WHERE SERIALA.SERIAL = SERIAL.SERIAL
AND SERIALSTATUS IN (2,8,4)
AND EXISTS(
SELECT 'X' FROM TRANSORDER
WHERE TRANSORDER.TYPE = 'B'
AND SERIAL = SERIAL.SERIAL);
/*--*/
OPEN E1;
GOTO 19 WHERE :RETVAL <= 0;
LABEL 10;
:SERIALERR = 0;
:PAR1 = '';
FETCH E1 INTO :SERIALERR,:PAR1;
GOTO 18 WHERE :RETVAL <=0;
WRNMSG 20 WHERE :SERIALERR > 0;
LOOP 10;
LABEL 18;
CLOSE E1;
LABEL 19;
/*-*/
DECLARE E2 CURSOR FOR
SELECT PART,PARTNAME
FROM PART P
WHERE EXISTS(
SELECT 'X' FROM SERIAL,SERIALA
WHERE SERIALA.SERIAL = SERIAL.SERIAL
AND SERIALSTATUS IN (2,8,4)
AND SERIAL.PART = P.PART)
AND EXISTS(
SELECT 'X' FROM PARTALT,PART P2
WHERE PARTALT.PART = P.PART
AND P2.PART = PARTALT.ALT
AND P2.TYPE = 'P');
/*--*/
OPEN E2;
GOTO 29 WHERE :RETVAL <= 0;
LABEL 20;
:SERIALERR = 0;
:PAR1 = '';
FETCH E2 INTO :SERIALERR,:PAR1;
GOTO 28 WHERE :RETVAL <= 0;
WRNMSG 22 WHERE :SERIALERR > 0;
LOOP 20;
LABEL 28;
CLOSE E2;
LABEL 29;
DISPLAY 1 OF 10;
/*-----End Tests-----------*/
INSERT INTO SIL_DEFICIENCIES_RUN(RUNDATE,USER,SAVE)
VALUES(SQL.DATE,SQL.USER,:$.SAV);
/*---*/
LINK URI_STACK_B TO :$.STB;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK URI_STACK_C TO :$.STC;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK URI_STACK_p TO :$.STP;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK URI_STACK_E TO :$.STE;
ERRMSG 1 WHERE :RETVAL <= 0;
/*---*/
DELETE FROM SIL_DEFICIENCIES;
DELETE FROM SIL_DEFICIENCIES_S;
DELETE FROM SIL_DEFICIENCIES_XL;
DELETE FROM SIL_DEFICIENCIES_XLP;
DELETE FROM SIL_DEFICIENCIES_P;
DELETE FROM SIL_DEFICIENCIES_D;
DELETE FROM SIL_DEFICIENCIES_SER;
DISPLAY 2 OF 10;
/*---------CALCULATE ALT TYPE 'R' TO TYPE 'P' STOCK -------------*/
INSERT INTO URI_STACK_B(KEY1,KEY2,INTVAL1)
SELECT P.PART,A.PART,SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,PARTALT,PART P,PART A
WHERE PARTALT.PART = P.PART
AND A.PART = PARTALT.ALT
AND P.TYPE = 'P'
AND A.PART > 0
AND A.TYPE = 'R'
AND WARHSBAL.PART = A.PART
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS NOT IN(0,966,6,1365) /* FIX GF*/
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND NOT (WARHSNAME ='ACC' AND LOCNAME LIKE 'GF%')
AND WARHSBAL.CUST NOT IN(-2,-3,-5)
GROUP BY 1,2;
/*---------CALCULATE ALT TYPE 'R' TO TYPE 'P' PORDERS-------------*/
INSERT INTO URI_STACK_B(KEY1,KEY2,KEY3,INTVAL1)
SELECT P.PART,A.PART,DUEDATE,SUM(ABALANCE/100)
FROM PORDERITEMS,PARTALT,PART P,PART A
WHERE P.PART = PARTALT.PART
AND A.PART = PARTALT.ALT
AND P.TYPE = 'P'
AND A.TYPE = 'R'
AND A.PART > 0
AND CLOSED <> 'C'
AND PORDERITEMS.PART = A.PART
GROUP BY 1,2,3;
/*-Update SIL_DEFICIENCIES_XLP according to porders from exland--*/
INSERT INTO SIL_DEFICIENCIES_XLP(PART)
SELECT PART
FROM PORDERS,PORDERITEMS
WHERE PORDERS.ORD = PORDERITEMS.ORD
AND PORDERS.SUP = 690 /*Exland*/
AND PORDERITEMS.CLOSED <> 'C';
DISPLAY 3 OF 10;
/*--------ENTER THE ORDERS INITIAL DEMANDS-------*/
INSERT INTO SIL_DEFICIENCIES(ORDI,ORIG,PART,DUEDATE,DEMAND,ODEMAND,
INTVAL1,LEVEL,PRIORITY)
SELECT ORDERITEMS.ORDI,ORDERITEMS.PART,ORDERITEMS.PART,
ORDERITEMS.DUEDATE,
ORDERITEMS.ABALANCE  / 100,ORDERITEMS.ABALANCE/100,
(PART.FAMILY = 3 ? 2 : 0),/*IF EMD ENTER THE EMD'S LOOP*/
-1,
(SIL_ORDISINGLE.FINAL = 'Y' ? 1 :
(SIL_ORDISINGLE.CSTAT = 42 ? 0 :
(SIL_ORDISINGLE.CSTAT = 19 ? 3 : 2)))
FROM ORDERITEMS,PART P,PART,ORDERS,SIL_ORDISINGLE ?
WHERE ORDERITEMS.CLOSED <> 'C'
AND SIL_ORDISINGLE.CSTAT <> 42
AND SIL_ORDISINGLE.ORDI = ORDERITEMS.ORDI
AND ORDERITEMS.PART = PART.PART
AND ORDERITEMS.DUEDATE BETWEEN :$.FDT AND :$.TDT
AND ABALANCE <> 0
AND ORDERS.ORD = ORDERITEMS.ORD
AND ORDTYPE NOT IN (2,3,4)
AND PART.PARTNAME NOT LIKE '%FE';
DISPLAY 4 OF 10;
/*-----STACK THE INITAL PARTS STOCK---------------------*/
INSERT INTO SIL_DEFICIENCIES_S(PART,STOCK,INITIALSTOCK)
SELECT WARHSBAL.PART,SUM(BALANCE/100),SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,CUSTOMERS
WHERE EXISTS(
SELECT 'X' FROM SIL_DEFICIENCIES
WHERE SIL_DEFICIENCIES.PART = WARHSBAL.PART)
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS NOT IN(0,966,6,1365,1420) /* FIX GF*/
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND NOT (WARHSNAME ='ACC' AND LOCNAME LIKE 'GF%')
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND CUSTOMERS.CUST NOT IN(-2,-3,-5)
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB05%'
GROUP BY 1;
/*----STACK ALL DEMANDED PARTS STOCK---------------------------*/
INSERT INTO SIL_DEFICIENCIES_S(PART,STOCK,INITIALSTOCK)
SELECT WARHSBAL.PART,SUM(BALANCE/100),SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,CUSTOMERS
WHERE EXISTS(
SELECT 'X' FROM SIL_DEFICIENCIES,SIL_FLATTREE
WHERE SIL_DEFICIENCIES.PART = SIL_FLATTREE.PARENT
AND WARHSBAL.PART =SIL_FLATTREE.SON)
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS <> 0
AND WARHSBAL.WARHS NOT IN(966,6,1420,1365)
/* PACK-FIX-GF F-GF F-SE F-RD SPL?*/
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND NOT (WARHSNAME ='ACC' AND LOCNAME LIKE 'GF%')
AND WARHSNAME NOT LIKE '%XL' /*X-LAND*/
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND CUSTOMERS.CUST NOT IN(-2,-3)
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB05%'
GROUP BY 1;
DISPLAY 6 OF 10;
/*-----------NV---NV---NV---NV---NV---NV---NV---NV-------------*/
INSERT INTO SIL_DEFICIENCIES_S(PART)
SELECT P1.PART
FROM PART P1,PART P2
WHERE P2.PARTNAME = STRCAT(P1.PARTNAME,'NV')
AND EXISTS(
SELECT 'X' FROM WARHSBAL WHERE PART = P2.PART
);
/*-*/
DECLARE NV CURSOR FOR
SELECT P1.PART ,SUM(BALANCE)/100
FROM PART P1 ,PART P2,SIL_DEFICIENCIES_S,
WAREHOUSES,WARHSBAL,CUSTOMERS
WHERE P2.PARTNAME = STRCAT(P1.PARTNAME,'NV')
AND P1.PART = SIL_DEFICIENCIES_S.PART
AND WARHSBAL.PART = P2.PART
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS NOT IN(0,966,6,1365) /* FIX GF*/
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND WARHSNAME NOT LIKE '%XL' /*X-LAND*/
AND NOT (WARHSNAME ='ACC' AND LOCNAME LIKE 'GF%')
AND WARHSBAL.CUST NOT IN(-2,-3,-5)
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB05%'
GROUP BY 1;
OPEN NV;
GOTO 110 WHERE :RETVAL <= 0;
/*-*/
LABEL 100;
:NV = 0;
FETCH NV INTO :NV,:STOCK;
GOTO 109 WHERE :RETVAL <= 0;
UPDATE SIL_DEFICIENCIES_S
SET STOCK = STOCK + :STOCK,
INITIALSTOCK = INITIALSTOCK +:STOCK
WHERE PART = :NV;
/*-*/
LOOP 100;
LABEL 109;
CLOSE NV;
LABEL 110;
DISPLAY 7 OF 10;
/*--------------GET OPEN SERIAL DEMAND------------------*/
DECLARE PARTIAL CURSOR FOR
SELECT SERIAL.SERIAL,SERIAL.PART,QUANT/100
FROM SERIAL,SERIALA,PART P
WHERE SERIALA.SERIAL = SERIAL.SERIAL
AND SERIALA.SERIALSTATUS = 5
AND P.PART = SERIAL.PART
AND P.FAMILY = 3;
/*-*/
OPEN PARTIAL;
GOTO 209 WHERE :RETVAL <= 0;
LABEL 200;
:S = :P = :Q = :USED = 0;
FETCH PARTIAL INTO :S,:P,:Q;
GOTO 208 WHERE :RETVAL <= 0;
/*-*/
SELECT SUM(QUANT)/100 INTO :USED
FROM TRANSORDER
WHERE SERIAL = :S
AND TYPE = 'B';
/*--*/
:TOTAL = :Q - :USED;
LOOP 200 WHERE :TOTAL <= 0;
INSERT INTO SIL_DEFICIENCIES_SER(SERIAL,PART,QUANT)
VALUES(:S,:P,:TOTAL);
/*---*/
LOOP 200;
LABEL 208;
CLOSE PARTIAL;
LABEL 209;
/*---*/
INSERT INTO SIL_DEFICIENCIES_SER(SERIAL,PART,QUANT)
SELECT SERIAL.SERIAL,SERIAL.PART,QUANT/100
FROM SERIAL,SERIALA,PART P
WHERE SERIALA.SERIAL = SERIAL.SERIAL
AND P.PART = SERIAL.PART
AND P.FAMILY = 3
AND SERIALA.SERIALSTATUS IN (2,8,4);
/*-*/
DISPLAY 8 OF 10;
/************************************************/
DECLARE MAIN CURSOR FOR
SELECT DISTINCT
S.ORDI,S.PART,S.DEMAND,S.DUEDATE,S.PRIORITY,ORDERS.CURDATE
FROM SIL_DEFICIENCIES S,ORDERS,ORDERITEMS
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = S.ORDI
ORDER BY 5,4,6,3,2;
OPEN MAIN;
:N = :RETVAL;
:I = 0;
GOTO 801 WHERE :N <= 0;
/*--------------*/
LABEL 600;
:I = :I +1;
DISPLAY :I OF :N;
:ORDI = :DATE = :DEMAND = :PRIORITY = :PART = :DD = 0;
FETCH MAIN INTO :ORDI,:PART,:DEMAND,:DATE,:PRIORITY,:DD;
GOTO 800 WHERE :RETVAL <= 0;
/*--------------RUN ON LEVEL 0---------------*/
LABEL 510;
:BAL = 0;
SELECT STOCK INTO :BAL
FROM SIL_DEFICIENCIES_S
WHERE PART = :PART;
/*----------*/
:BAC = 0;
:BAC = (:BAL > 0 ? (:BAL >= :DEMAND ? :DEMAND : :BAL) : 0);
GOTO 520 WHERE :BAC = 0;
:BAL = :BAL - :BAC;
:DEMAND = :DEMAND - :BAC;
/*--------------*/
UPDATE SIL_DEFICIENCIES
SET DEMAND = :DEMAND,
STOCK = :BAC,
SDEMAND = :X
WHERE ORDI = :ORDI
AND PART = :PART;
LABEL 520;
/*--------------*/
UPDATE SIL_DEFICIENCIES_S
SET STOCK = :BAL
WHERE PART = :PART;
/**************************************************/
:COUNT = 0;
LABEL 700;
:CCC = :COUNT;
:CH = 0;
SELECT COUNT(*) INTO :CH FROM SIL_DEFICIENCIES
WHERE INTVAL1 =:COUNT/:COUNT
AND ORDI = :ORDI;
GOTO 799 WHERE :CH = 0;
/*--*/
INSERT INTO SIL_DEFICIENCIES(ORDI,ORIG,PART,DEMAND,DUEDATE,ODEMAND,
INTVAL1,LEVEL,PARENT,INTVAL2,PRIORITY)
SELECT ORDI,P.PART,P.PART,DEMAND * COEF ,DUEDATE,
DEMAND * COEF,2,:COUNT + 1,PARTARC.PART,(STOCK + INTVAL2) *
COEF,PRIORITY
FROM SIL_DEFICIENCIES,PART P,PARTARC
WHERE PARTARC.PART = SIL_DEFICIENCIES.PART
AND P.PART = PARTARC.SON
AND P.TYPE = 'P'
AND ORDI = :ORDI
AND SQL.DATE BETWEEN PARTARC.FROMDATE AND PARTARC.TILLDATE
AND DEMAND > 0
AND INFOONLY <> 'Y'
AND INTVAL1 = (:COUNT/:COUNT);
/*----------SUBTRACT STOCK------------*/
:COUNT = :COUNT + 1;
DECLARE RECUR CURSOR FOR
SELECT  SIL_DEFICIENCIES.PART,DEMAND,LEVEL,PARENT,ISALT,INTVAL2
FROM SIL_DEFICIENCIES,PART P
WHERE P.PART = SIL_DEFICIENCIES.PART
AND ORDI = :ORDI
AND INTVAL1 = 2
AND DEMAND > 0
ORDER BY 2;
OPEN RECUR;
GOTO 799 WHERE :RETVAL <= 0;
/*------------------*/
LABEL 710;
:PART = :DEMAND = :PARENT = :L = :ISALT = :FSTOCK = 0;
FETCH RECUR INTO :PART,:DEMAND,:L,:PARENT ,:ISALT, :FSTOCK;
GOTO 798 WHERE :RETVAL <= 0;
/*-------------------------------*/
:BAL = :OBAL = :BAC = :XBAL = :XOBAL = :XBAC = 0;
/*----------CALC DEMAND FROM SERIALS----*/
:DECREASESTOCK = :SDM = :MDEMAND2 = 0;
DECLARE SERIALS CURSOR FOR
SELECT SIL_DEFICIENCIES_SER.SERIAL,QUANT,USED
FROM SIL_DEFICIENCIES_SER,ORDSERIAL
WHERE SIL_DEFICIENCIES_SER.SERIAL = ORDSERIAL.SERIAL
AND ORDSERIAL.ORDI = :ORDI
AND SIL_DEFICIENCIES_SER.PART = :PART
AND (QUANT > 0 OR USED > 0);
OPEN SERIALS;
GOTO 716 WHERE :RETVAL <= 0;
LABEL 711;
:SER = :SERDEM = :USED = 0;
FETCH SERIALS INTO :SER,:SERDEM,:USED;
GOTO 715 WHERE :RETVAL <= 0;
GOTO 712 WHERE :SERDEM < 1;
/*---decresing demand on serial balance--------*/
:USED = (:SERDEM > :DEMAND ? :SERDEM - :DEMAND : 0);
:DEMAND = (:DEMAND > :SERDEM ? :DEMAND : :SERDEM);
:SDM = :SERDEM;
/*----------*/
SELECT 1 INTO :DECREASESTOCK FROM DUMMY WHERE :DEMAND = :SERDEM;
GOTO 714 WHERE :DEMAND = :SERDEM;
LABEL 712;
/*--DECREASE STOCK ONLY IF THERE ARE SERIAL DEMANDS---*/
GOTO 713 WHERE :DECREASESTOCK = 1;
SELECT STOCK,OSTOCK  INTO :BAL,:OBAL
FROM SIL_DEFICIENCIES_S
WHERE PART = :PART;
:BAC = (:BAL > 0 ? (:BAL >= :DEMAND ? :DEMAND : :BAL) : 0);
:DEMAND = :DEMAND - :BAC;
:DECREASESTOCK = 1;
LABEL 713;
/*---increase serial use by demand---*/
:MDEMAND1 = :DEMAND;
:DEMAND = (:DEMAND > :USED ? :DEMAND - :USED : 0);
:USED = (:USED < :MDEMAND1 ? 0 : :USED - :MDEMAND1);
:MDEMAND2 = :DEMAND - :MDEMAND1;
LABEL 714;
/*--*/
UPDATE SIL_DEFICIENCIES_SER
/*SET QUANT = 0,*/
SET QUANT = (:SERDEM > 0 ? QUANT - :SERDEM : 0),
USED = :USED,
ORDI = (ORDI > 0 ? ORDI : :ORDI)
WHERE SERIAL = :SER;
/*---*/
LOOP 711;
LABEL 715;
CLOSE SERIALS;
LABEL 716;
/*DECREASE STOCK ONLY IF THERE ARE NO SERIAL DEMANDS*/
GOTO 717 WHERE :DECREASESTOCK = 1;
SELECT STOCK,OSTOCK  INTO :BAL,:OBAL
FROM SIL_DEFICIENCIES_S
WHERE PART = :PART;
:BAC = (:BAL > 0 ? (:BAL >= :DEMAND ? :DEMAND : :BAL) : 0);
:DEMAND = :DEMAND - :BAC;
LABEL 717;
/*-------END SERIAL CALC------------*/
:ALTP = 0;
SELECT ALT INTO :ALTP
FROM PARTALT,PART P
WHERE PARTALT.PART = :PART
AND P.PART = PARTALT.ALT
AND P.TYPE = 'P'
AND (PARENT = 0 OR EXISTS(
SELECT 'X' FROM SIL_FLATTREE,SIL_DEFICIENCIES SDALT
WHERE SIL_FLATTREE.PARENT = PARTALT.PARENT
AND SDALT.PART = SIL_FLATTREE.PARENT
AND SDALT.ORDI = :ORDI
AND SON = PARTALT.PART));
GOTO 714 WHERE :ALTP = 0;
/*-*/
INSERT INTO SIL_DEFICIENCIES_S(PART,STOCK,INITIALSTOCK)
SELECT WARHSBAL.PART,SUM(BALANCE/100),SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,CUSTOMERS
WHERE EXISTS(
SELECT 'X' FROM SIL_FLATTREE
WHERE SIL_FLATTREE.PARENT = :ALTP
AND WARHSBAL.PART =SIL_FLATTREE.SON)
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS <> 0
AND WARHSBAL.WARHS NOT IN(966,6,1420,1365)
/* PACK-FIX-GF F-GF F-SE F-RD SPL?*/
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND NOT (WARHSNAME ='ACC' AND LOCNAME LIKE 'GF%')
AND WARHSNAME NOT LIKE '%XL' /*X-LAND*/
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND CUSTOMERS.CUST NOT IN(-2,-3)
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB05%'
GROUP BY 1;
/*-*/
LABEL 714;
/*---*/
:ALTR = :AAA = 0;
SELECT KEY2,INTVAL1 INTO :AAA,:ALTR
FROM URI_STACK_B
WHERE KEY1 = :PART
AND KEY3 = 0
AND INTVAL1 > 0;
GOTO 715 WHERE :RETVAL <= 0;
/*--------------------*/
:D = :DEMAND;
:DEMAND = (:DEMAND >= :ALTR ? :DEMAND - :ALTR : 0);
:ALTRBAC = (:ALTR >= :D ? :ALTR - :D : 0);
UPDATE URI_STACK_B
SET INTVAL1 = :ALTRBAC
WHERE KEY1 = :PART
AND KEY2 = :AAA
AND KEY3 = 0;
/*-*/
:ALTR = :ALTR - :ALTRBAC;
LABEL 715;
/*-------------ALT R------------*/
:ALTRP = 0;
:OLDD = :DEMAND;
DECLARE ALTRPORD CURSOR FOR
SELECT KEY1,KEY2,KEY3,INTVAL1
FROM URI_STACK_B
WHERE KEY1 = :PART
/*AND KEY3 < :DATE *//*TEMPORARY*/
AND KEY3 > 0
AND INTVAL1 > 0
ORDER BY 3;
OPEN ALTRPORD;
GOTO 719 WHERE :RETVAL <= 0;
/*------------------------*/
LABEL 716;
:P = :A = :DUE = :Q = 0;
FETCH ALTRPORD INTO :P,:A,:DUE,:Q;
GOTO 718 WHERE :RETVAL <= 0;
/*------------*/
:ALTRP = (:Q >= :DEMAND ? :DEMAND : :Q);
:DEMAND = :DEMAND - :ALTRP;
:ALTR = :ALTR + :ALTRP;
/*--------------*/
UPDATE URI_STACK_B
SET INTVAL1 = INTVAL1 - :ALTRP
WHERE KEY1 = :P
AND KEY2 = :A
AND KEY3 = :DUE;
/*--------------*/
GOTO 718 WHERE :DEMAND = 0;
/*-------------*/
LOOP 716;
LABEL 718;
CLOSE ALTRPORD;
LABEL 719;
/*--------------------------------*/
GOTO 725 WHERE :ALTP = 0
OR :DEMAND = 0
OR :SERDEM <> 0 ;
/*----------:ALTP > 0-------------*/
INSERT INTO SIL_DEFICIENCIES(ORDI,ORIG,PART,DUEDATE,DEMAND,ODEMAND,
INTVAL1,LEVEL,ISALT,INTVAL1,INTVAL2,PRIORITY,PARENT)
VALUES( :ORDI,:PART,:ALTP,:DATE,:DEMAND,
:DEMAND , 2, :COUNT + 1 ,1,3 ,:BAC,:PRIORITY,:PART);
:DEMAND  = 0;
/*-*/
INSERT INTO SIL_DEFICIENCIES_S(PART,STOCK,INITIALSTOCK)
SELECT WARHSBAL.PART,SUM(BALANCE/100),SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,CUSTOMERS
WHERE EXISTS(
SELECT 'X' FROM SIL_DEFICIENCIES
WHERE PART = :ALTP
AND SIL_DEFICIENCIES.PART = WARHSBAL.PART)
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS NOT IN(0,966,6,1365,1420)
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND WARHSBAL.CUST NOT IN(-2,-3,-5)
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND NOT (WARHSNAME ='ACC' AND LOCNAME LIKE 'GF%')
AND WARHSNAME NOT LIKE 'XL%'
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
GROUP BY 1;
/*---------------*/
LABEL 725; /*OR 740*/
/*----------*/
UPDATE SIL_DEFICIENCIES
SET DEMAND = :DEMAND,
STOCK = :BAC + :XBAC,
ALTR = :ALTR,
SDEMAND = (:SDM > 0 ? :SDM : :MDEMAND2)
WHERE ORDI = :ORDI
AND PART = :PART
AND PARENT = :PARENT;
/*--------------*/
GOTO 730 WHERE :ALTR = 0;
INSERT INTO SIL_DEFICIENCIES(ORDI,ORIG,PART,DUEDATE,DEMAND,ODEMAND,
INTVAL1,LEVEL,PARENT,ISALT,PRIORITY)
VALUES(
:ORDI,:PART,:AAA,:DATE,:ALTR,:ALTR,2,:PART,:PART,1,:PRIORITY);
/*-*/
INSERT INTO SIL_DEFICIENCIES_S(PART,STOCK)
SELECT PART,SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,CUSTOMERS
WHERE PART = :AAA
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS NOT IN(0,966,6,1365,1420)
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND WARHSBAL.CUST NOT IN(-2,-3,-5)
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
GROUP BY 1;
/*-*/
LABEL 730;
/*--------------------*/
/*GOTO 732 WHERE :BAC <= 0;*/
UPDATE SIL_DEFICIENCIES_S
SET STOCK = STOCK - :BAC,
OSTOCK = OSTOCK + :SERBAC,
FSTOCK = FSTOCK + :FSTOCK
WHERE PART = :PART;
LABEL 732;
/*---------------------*/
/*GOTO 733 WHERE :XBAC <= 0;*/
UPDATE SIL_DEFICIENCIES_XL
SET STOCK = STOCK - :XBAC
WHERE PART = :PART;
LABEL 733;
/*---------------------*/
LOOP 710;
LABEL 798;
CLOSE RECUR;
/*------------*/
UPDATE SIL_DEFICIENCIES
SET INTVAL1 = INTVAL1 -1
WHERE INTVAL1 > 0;
/*----------*/
LOOP 700;
LABEL 799;
LOOP 600;
LABEL 800;
CLOSE MAIN;
LABEL 801;
UPDATE SIL_DEFICIENCIES SET INTVAL1 = 0;
/*-----------------STOP------------------------*/
/*הורדת הדרישה להזמנה מקושרת לפק"ע המיוצרת למחסן לא כלול במלאי*/
DECLARE DEV CURSOR FOR
SELECT SD.PART,SD.ORDI,SD.ORIG,SUM(TRANSORDER.QUANT/100)
FROM SIL_DEFICIENCIES SD, SERIALA,
WAREHOUSES,TRANSORDER,SERIAL,PART,ORDSERIAL
WHERE PART.PART = TRANSORDER.PART
AND SERIAL.SERIAL = TRANSORDER.SERIAL
AND SERIAL.PART = PART.PART
AND SERIAL.SERIAL = SERIALA.SERIAL
AND ORDSERIAL.SERIAL = SERIAL.SERIAL
AND ORDSERIAL.ORDI = SD.ORDI
AND SERIAL.PART = SD.PART
AND PART.FAMILY = 3
AND SD.DEMAND  >0
/*AND SERIALA.SERIALSTATUS  =5*/
AND TRANSORDER.TYPE  ='B'
AND TRANSORDER.TOWARHS   = WAREHOUSES.WARHS
AND (WAREHOUSES.WARHSNAME = 'DEV'
OR WAREHOUSES.WARHSNAME='GEF'
OR WAREHOUSES.WARHS IN(0,966,6,1365,1420))
GROUP BY 1,2,3;
/*-*/
OPEN DEV;
GOTO 810 WHERE :RETVAL <= 0;
LABEL 805;
:PA = :ORD = :OG = :Q = 0;
FETCH DEV INTO :PA,:ORD,:OG,:Q;
GOTO 809 WHERE :RETVAL <= 0;
/*-------*/
UPDATE SIL_DEFICIENCIES SET DEMAND = DEMAND - :Q
WHERE PART = :PA
AND ORDI = :ORD
AND ORIG = :OG;
/*-------------------*/
LOOP 805 ;
LABEL 809;
CLOSE DEV;
LABEL 810;
/*-------------------------*/
DISPLAY 1 OF 10;
INSERT INTO SIL_DEFICIENCIES(ORDI,ORIG,PART,DUEDATE,DEMAND,ODEMAND,
INTVAL1,LEVEL,PARENT,PRIORITY)
SELECT ORDI,P.PART,P.PART,DUEDATE,
DEMAND * COEF ,DEMAND * COEF,0,PARTARC.PART,PARTARC.PART,PRIORITY
FROM SIL_DEFICIENCIES,PART P,PARTARC
WHERE PARTARC.PART = SIL_DEFICIENCIES.PART
AND SQL.DATE BETWEEN PARTARC.FROMDATE AND PARTARC.TILLDATE
AND P.PART = SON
AND P.TYPE = 'R'
AND PARTARC.INFOONLY <> 'Y'
AND DEMAND > 0
/*EXLAND לא לדרוש רכיבי מכלולי*/
AND NOT EXISTS(
SELECT 'X' FROM PART WHERE PART = PARTARC.PART AND PROC = 264)
/*מסומן 'לא ליצור חוסרים' ומכלול ולא מקושר לפק"ע */
AND NOT EXISTS(
SELECT 'X' FROM SIL_DEFICIENCIES_EXC E
WHERE E.PART IN(PARTARC.PART,PARTARC.SON)
AND E.ORDI = SIL_DEFICIENCIES.ORDI
/*AND SIL_DEFICIENCIES.SDEMAND = 0*/
);
DISPLAY 2 OF 10;
/*--------BULK-----------*/
UPDATE SIL_DEFICIENCIES
SET DEMAND = DEMAND + 20
WHERE ORDI IN(
SELECT  ORDI FROM SIL_DEFICIENCIES_SER
WHERE ORDI > 0
AND EXISTS(
SELECT 'X' FROM KITITEMS,PART P
WHERE KITITEMS.PART = SIL_DEFICIENCIES.PART
AND KITITEMS.SERIAL = SIL_DEFICIENCIES_SER.SERIAL
AND P.PART = KITITEMS.PART
AND KITITEMS.QUANT > 4900
AND P.KITFLAG <> 'Y'));
/*-*/
UPDATE SIL_DEFICIENCIES
SET DEMAND = DEMAND + 30
WHERE ORDI IN(
SELECT  ORDI FROM SIL_DEFICIENCIES_SER
WHERE ORDI > 0
AND EXISTS(
SELECT 'X' FROM KITITEMS,PART P ,PARTPARAM PA
WHERE KITITEMS.PART = SIL_DEFICIENCIES.PART
AND KITITEMS.SERIAL = SIL_DEFICIENCIES_SER.SERIAL
AND KITITEMS.QUANT > 4900
AND P.PART = KITITEMS.PART
AND PA.PART = P.PART
AND (P.SECONDCOST < 0.2 OR PA.MINPURQUANT >= 300000)
AND P.KITFLAG <> 'Y'));
DISPLAY 3 OF 10;
/*------איתחול נתוני הזמנות רכש לרכיבים-------*/
INSERT INTO SIL_DEFICIENCIES_P(PART,DUEDATE,STOCK)
SELECT P.PART,
DUEDATE,
SUM(ABALANCE/100)
FROM PORDERITEMS,PART P,PORDERS
WHERE PORDERITEMS.CLOSED <> 'C'
AND P.PART = PORDERITEMS.PART
AND PORDERS.SUP <> 251 /*Exland*/
AND P.TYPE = 'R'
AND PORDERS.ORD = PORDERITEMS.ORD
AND PORDERS.UFLAG = 'Y'
AND DEAL=0 /*NO CONNECTION TO DEALS*/
GROUP BY 1,2;
/*----איתחול נתוני הזמנות מסגרת  לרכיבים----*/
INSERT INTO SIL_DEFICIENCIES_D(PART,STOCK)
SELECT PART,SUM(TBALANCE/100)
FROM PDEALITEMS,PDEAL
WHERE PDEALITEMS.CLOSED <> 'C'
AND PDEAL.DEAL = PDEALITEMS.DEAL
AND UFLAG = 'Y'
AND TBALANCE > 0
GROUP BY 1;
/*----*/
DISPLAY 4 OF 10;
/*DELETE FROM URI_STACK_B;*/
INSERT INTO URI_STACK_C(KEY1,KEY2,INTVAL1)
SELECT ALT,ORIG,PARTALT.PARENT
FROM PARTALT,SIL_DEFICIENCIES S, PART AL
WHERE PARTALT.PART = S.ORIG
AND AL.PART = ALT
AND AL.TYPE = 'R'
AND AL.PARTNAME NOT LIKE '%NV'
AND LEVEL > 10
/*---- EXISTS STOCK ----*/
AND (PARTALT.PARENT = 0
OR EXISTS(    /*----- ALTERNATIVE IN THIS SPESIFIC CASE---------*/
SELECT 'X' FROM SIL_FLATTREE,SIL_DEFICIENCIES SDALT
WHERE SIL_FLATTREE.PARENT = PARTALT.PARENT
AND SDALT.PART = SIL_FLATTREE.PARENT
AND SDALT.ORDI = S.ORDI
AND SDALT.LEVEL < 10
AND SDALT.DEMAND > 0
AND SON = PARTALT.PART
));
/*--*--*--*--*--*--*--*-יצור בחוסר-*--*--*--*--*--*--*--*--*/
DISPLAY 5 OF 10;
DECLARE DEFDEF CURSOR FOR
SELECT S.SERIAL,SDD.PART,SDD.QUANT
FROM SIL_DEFICIENCIES_DEF SDD,SERIAL S
WHERE S.SERIAL = SDD.SERIAL
AND S.CLOSED = 'C';
OPEN DEFDEF;
GOTO 852 WHERE :RETVAL <=0;
LABEL 840;
:SERIAL = :PART = :QUANT = 0;
FETCH DEFDEF INTO :SERIAL,:PART,:QUANT;
GOTO 851 WHERE :RETVAL <=0;
/*---מביא את מק"ט האב ושורת ההמנה הראשונה המקושרת---*/
:PPART = :ORDI = 0;
SELECT S1.PART,MIN(OI.ORDI) INTO :PPART,:ORDI
FROM ORDERITEMS OI, SERIAL S1 ,ORDSERIAL OS
WHERE S1.SERIAL = :SERIAL
AND OS.SERIAL = S1.SERIAL
AND OI.ORDI = OS.ORDI
AND OI.CLOSED <> 'C'
GROUP BY 1;
/*-------------*/
INSERT INTO SIL_DEFICIENCIES(ORDI,ORIG,PART,PARENT,LEVEL,DEMAND)
VALUES(:ORDI,:PART,:PART,:PPART,:PPART,:QUANT);
GOTO 845 WHERE :RETVAL > 0;
UPDATE SIL_DEFICIENCIES
SET DEMAND = DEMAND + :QUANT
WHERE PARENT = :PPART
AND PART = :PART
AND ORDI = :ORDI;
/*---*/
LABEL 845;
LOOP 840;
LABEL 851;
CLOSE DEFDEF;
LABEL 852;
DISPLAY 8 OF 10;
/*=================RUN ON R DEMANDS===================*/
/*:PAR1 = 'BEFORE R';
WRNMSG 2;*/
DECLARE RS CURSOR FOR
SELECT
ORDI,ORIG,PART,DEMAND,DUEDATE,ALTR,ISALT,LEVEL,PRIORITY,PARENT
FROM SIL_DEFICIENCIES
WHERE LEVEL >= 10
AND DEMAND > 0
ORDER BY 9,5,4;
OPEN RS;
:N = :RETVAL;
:I = 0;
GOTO 999 WHERE :N <= 0;
/*------------------*/
LABEL 910;
:I = :I +1;
DISPLAY :I OF :N;
:ORDI = :ORIG = :PART = :DEMAND = :DATE = :ISALT = :ALTR = :LEVEL =
:PR = :PARENT = 0;
FETCH RS INTO
:ORDI,:ORIG,:PART,:DEMAND,:DATE,:ISALT,:ALTR,:LEVEL,:PR,:PARENT;
GOTO 998 WHERE :RETVAL <= 0;
/*-----------------*/
GOTO 990 WHERE :DEMAND <= 0;
/*--------------------הורדת מלאי זמין-------------------*/
:BAL = 0;
SELECT STOCK INTO :BAL
FROM SIL_DEFICIENCIES_S
WHERE PART = :PART;
/*----------*/
:BALBAC = 0;
:BALBAC = (:BAL > 0 ? (:BAL >= :DEMAND ? :DEMAND : :BAL) : 0);
GOTO 915 WHERE :BALBAC = 0;
:BAL = :BAL - :BALBAC;
:DEMAND = :DEMAND - :BALBAC;
/*--------------*/
UPDATE SIL_DEFICIENCIES_S
SET STOCK = :BAL
WHERE PART = :PART;
LABEL 915;
GOTO 990 WHERE :DEMAND <= 0;
/*--------------PORDERS--PORDES--PORDES--PORDES----------------*/
:LEVELFAMILY = :PDATE = 0;
SELECT FAMILY INTO :LEVELFAMILY FROM PART WHERE PART = :LEVEL;
:PDATE = :DATE - (:LEVELFAMILY = 3 ? 20 : 6) * 1440;
/*-*/
DECLARE PORD CURSOR FOR
SELECT DUEDATE,STOCK
FROM SIL_DEFICIENCIES_P
WHERE PART = :PART
AND STOCK > 0
ORDER BY 1;
OPEN PORD;
GOTO 929 WHERE :RETVAL <= 0;
/*-----------------*/
:PORDQ = :FPORDQ = 0;
:PDEMAND = :DEMAND;
LABEL 920;
:PD = :QUANT = 0;
FETCH PORD INTO :PD,:QUANT;
GOTO 928 WHERE :RETVAL <= 0;
/*--------------*/
:PORDBAC = (:QUANT >= :DEMAND ? :DEMAND : :QUANT);
:PORDQ = :PORDQ + (:PD <= :PDATE ? :PORDBAC : 0);
:FPORDQ = :FPORDQ + (:PD > :PDATE ? :PORDBAC : 0);
:DEMAND = :DEMAND - (:PD <= :PDATE ? :PORDBAC : 0);
:PDEMAND = :PDEMAND - :PORDBAC;
/*--------------*/
UPDATE SIL_DEFICIENCIES_P
SET STOCK = STOCK - :PORDBAC
WHERE PART = :PART
AND DUEDATE = :PD;
/*--------------*/
GOTO 928 WHERE :PDEMAND <= 0;
LOOP 920;
LABEL 928;
CLOSE PORD;
LABEL 929;
/*----*/
GOTO 990 WHERE :DEMAND <= 0;
/*--------------DEAL---DEAL---DEAL---DEAL---------------*/
:DEAL = 0;
SELECT STOCK INTO :DEAL
FROM SIL_DEFICIENCIES_D
WHERE PART = :PART;
/*-----------------------*/
:DEALBAC = 0;
:DEALBAC = (:DEAL > 0 ? (:DEAL >= :DEMAND ? :DEMAND : :DEAL) : 0);
:DEMAND = :DEMAND - :DEALBAC;
/*--------------*/
GOTO 917 WHERE :DEALBAC = 0;
UPDATE SIL_DEFICIENCIES_D
SET STOCK = STOCK - :DEALBAC
WHERE PART = :PART;
LABEL 917;
/*--------------------*/
GOTO 990 WHERE :DEMAND <= 0;
/*---------------------------------ALT ALT ALT ALT ALT -----------*/
SELECT KEY1 FROM URI_STACK_C WHERE KEY2 = :PART;
GOTO 990 WHERE :RETVAL <= 0;
/*-----*/
INSERT INTO SIL_DEFICIENCIES
(ORDI,ORIG,PART,DEMAND,ODEMAND,DUEDATE,LEVEL,PARENT,ISALT,INTVAL1,
PRIORITY)
SELECT
:ORDI,:ORIG,KEY1,:DEMAND,:DEMAND,:DATE,:LEVEL,:LEVEL,1,KEY2,:PR
FROM URI_STACK_C
WHERE KEY2 = :PART;
GOTO 932 WHERE :RETVAL <= 0;
/*-----------------------------------*/
INSERT INTO SIL_DEFICIENCIES_S(PART,STOCK,INITIALSTOCK)
SELECT WARHSBAL.PART,SUM(BALANCE/100),SUM(BALANCE/100)
FROM WARHSBAL,WAREHOUSES,CUSTOMERS
WHERE EXISTS(
SELECT 'X' FROM URI_STACK_C
WHERE KEY1 = WARHSBAL.PART
AND KEY2 = :PART)
AND WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.BALANCE > 0
AND WARHSBAL.WARHS NOT IN(0,966,6,1365)
AND WARHSNAME NOT IN('DEV','GEF','SALE')
AND WARHSBAL.CUST NOT IN(-2,-3,-5)
AND CUSTOMERS.CUST = WARHSBAL.CUST
AND CUSTOMERS.CUSTNAME NOT LIKE '%MRB01%'
GROUP BY 1;
LABEL 932;
/*------------------------------------------------------*/
DECLARE ALT CURSOR FOR
SELECT
PART,DEMAND
FROM SIL_DEFICIENCIES
WHERE ORDI = :ORDI
AND ORIG = :PART
AND LEVEL = :LEVEL
AND ISALT = 1;
OPEN ALT;
GOTO 949 WHERE :RETVAL <= 0;
/*------------------*/
:COLLECT = 0;
LABEL 930;
:ALTPART = :ALTDEMAND = 0;
FETCH ALT INTO :ALTPART,:ALTDEMAND;
GOTO 948 WHERE :RETVAL <= 0;
:ALTDEMAND = (:COLLECT > 0 ? :COLLECT : :ALTDEMAND);
/*-------------RUN-----------------*/
:BAL = 0;
SELECT STOCK INTO :BAL
FROM SIL_DEFICIENCIES_S
WHERE PART = :ALTPART;
/*----------*/
:ALTBAC = 0;
:ALTBAC = (:BAL > 0 ? (:BAL >= :ALTDEMAND ? :ALTDEMAND : :BAL) : 0);
GOTO 935 WHERE :ALTBAC = 0;
:BAL = :BAL - :ALTBAC;
:ALTDEMAND = :ALTDEMAND - :ALTBAC;
/*--------------*/
UPDATE SIL_DEFICIENCIES_S
SET STOCK =  STOCK - :ALTBAC
WHERE PART = :ALTPART;
/*--------------*/
LABEL 935;
GOTO 945 WHERE :ALTDEMAND <= 0;
/*------------PORDERSALT--PORDESALT-PORDESALT-PORDESALT---------*/
DECLARE PORDALT2 CURSOR FOR
SELECT DUEDATE,STOCK
FROM SIL_DEFICIENCIES_P
WHERE PART = :ALTPART
AND STOCK > 0;
OPEN PORDALT2;
GOTO 939 WHERE :RETVAL <= 0;
/*-----------------*/
:APORDQ = :AFPORDQ = 0;
LABEL 936;
:PD = :QUANT = 0;
FETCH PORDALT2 INTO :PD,:QUANT;
GOTO 938 WHERE :RETVAL <= 0;
/*--------------*/
:APBAC = (:QUANT >= :ALTDEMAND ? :ALTDEMAND : :QUANT);
:APORDQ = :APORDQ + (:PD <= :PDATE ? :APBAC : 0);
:AFPORDQ = :AFPORDQ + (:PD > :PDATE ? :APBAC : 0);
:ALTDEMAND = :ALTDEMAND - (:PD <= :PDATE ? :APBAC : 0);
/*--------------*/
UPDATE SIL_DEFICIENCIES_P
SET STOCK = STOCK - :APBAC /*:QUANT*/
WHERE PART = :ALTPART
AND DUEDATE = :PD;
/*--------------*/
LOOP 936;
LABEL 938;
CLOSE PORDALT2;
LABEL 939;
GOTO 945 WHERE :ALTDEMAND <= 0;
/*--------------------*/
:DEAL = 0;
SELECT STOCK INTO :DEAL
FROM SIL_DEFICIENCIES_D
WHERE PART = :ALTPART;
:ADAC = 0;
:ADAC = (:DEAL > 0 ? (:DEAL >= :ALTDEMAND ? :ALTDEMAND : :DEAL) :
0);
:ALTDEMAND = :ALTDEMAND - :ADAC;
/*-*/
UPDATE SIL_DEFICIENCIES_D
SET STOCK = STOCK - :BAC
WHERE PART = :ALTPART;
/*--------write changes -----*/
LABEL 945;
UPDATE SIL_DEFICIENCIES
SET DEMAND = :ALTDEMAND,
STOCK = :ALTBAC,
DEAL = :ADAC,
PORD = :APORDQ,
FPORD = :AFPORDQ
WHERE ORDI = :ORDI
AND ISALT = 1
AND ORIG = :ORIG
AND LEVEL = :LEVEL /*LIKE PARENT*/
AND PART = :ALTPART;
/*--------------------*/
UPDATE SIL_DEFICIENCIES
SET DEMAND = :ALTDEMAND,
ODEMAND = :ALTDEMAND
WHERE ORDI = :ORDI
AND STOCK  = 0
AND PORD = 0
AND DEAL = 0
AND ORIG = :ORIG
AND ISALT =1
AND LEVEL = :LEVEL   /*LIKE PARENT*/
AND PART <> :ALTPART;
:ALTBAC = :ADAC = :APORDQ = :AFPORDQ = 0;
GOTO 948 WHERE :ALTDEMAND <= 0;
/*--------------*/
:COLLECT = :ALTDEMAND; /*MORE THEN ONE ALT IN CURSOR*/
LOOP 930;
LABEL 948;
CLOSE ALT;
/*------------*/
LABEL 949;
/*-----------------------*/
LABEL 990;
/*------------------------------*/
UPDATE SIL_DEFICIENCIES
SET DEMAND = :DEMAND,
STOCK = :BALBAC + :XBALBAC,
DEAL = :DEALBAC,
PORD = :PORDQ,
FPORD = :FPORDQ
WHERE ORDI = :ORDI
AND ORIG = :ORIG
AND LEVEL = :LEVEL  /*LIKE PARENT*/
AND PART = :PART;
:BALBAC = :XBALBAC = :DEALBAC = :PORDQ = :FPORDQ = :PDATE = 0;
LOOP 910;
/*---------------------*/
LABEL 998;
CLOSE RS;
LABEL 999;
LABEL 9990;
UNLINK URI_STACK_E;
UNLINK URI_STACK_p;
UNLINK URI_STACK_C;
UNLINK URI_STACK_B;
GOTO 9999 WHERE :$.SAV <> 'Y';
DELETE FROM SIL_DEFICIENCIES_BCK;
INSERT INTO SIL_DEFICIENCIES_BCK SELECT * FROM SIL_DEFICIENCIES;
LABEL 9999;