ORA-01400: невозможно вставить NULL в ("SUPERMAG"."SSLOCKS"."ID") при генерации документов по сличительной ведомости : Супермаг Плюс (Супермаг 2000)
15.12.2024 5:04
█ 07.09.2024 09:52
Есть уже тема по такой ошибке https://olegon.ru/showthread.php?t=23571, но там речь шла о регулярном техническом сбое, то есть сейчас, судя по всему, идет речь о какой-то логической ошибке.
Итак, сличительная ведомость, создаем документы на излишки и недосдачу, практически сразу
ORA-01400: невозможно вставить NULL в ("SUPERMAG"."SSLOCKS"."ID")
может, кто-то сталкивался? Вручную документы с такими номерами создать удается. Размерности колонок хватает, чтобы пихнуть туда этот номер. Собственно, магазин до этого работал и документы уже создавали, в том числе по ведомостям. Что-то ломается в SMGENERATEWAYSRL на 39 строке, понять бы, откуда там этот идентификатор берется... Что еще блокируется при создании документов по сличительной?
Итак, сличительная ведомость, создаем документы на излишки и недосдачу, практически сразу
ORA-01400: невозможно вставить NULL в ("SUPERMAG"."SSLOCKS"."ID")
может, кто-то сталкивался? Вручную документы с такими номерами создать удается. Размерности колонок хватает, чтобы пихнуть туда этот номер. Собственно, магазин до этого работал и документы уже создавали, в том числе по ведомостям. Что-то ломается в SMGENERATEWAYSRL на 39 строке, понять бы, откуда там этот идентификатор берется... Что еще блокируется при создании документов по сличительной?
█ 07.09.2024 14:16
Версию СМ между делом не обновляли? а то попадалась похожая ошибка, лечилась установкой последнего сервис пака.
█ 09.09.2024 13:15
Вот сейчас обновили до 1.052 SP4, ошибка повторяется... Попробовали экспортнуть сличительную в новую и по ней - не помогает. Помню, что была ошибка, но давно, вроде... Регресс что ли какой-то.
█ 09.09.2024 15:52
похоже что такая же ошибка, только версия древняя 1.037.1 SP7
(0.16Мб)
тогда помогла установка СП
и пересчет остатков в административном модуле
но все таки похоже что ошибки разные...
у тебя проблема с генерацией номера документа похоже... а у меня что то с остатками было
(0.16Мб)
тогда помогла установка СП
и пересчет остатков в административном модуле
но все таки похоже что ошибки разные...
у тебя проблема с генерацией номера документа похоже... а у меня что то с остатками было
█ 09.09.2024 17:23
Да, именно, что у меня на
Цитата:
но по таблице блокировок тоже точно была бага... OlegON ➤ SMGENERATEWAYSRL
█ 10.09.2024 00:10
В общем от тп получить кол запроса не удалось. Пришлось "вскрывать" самому)
Сначала вызывается процедура SMGenerateWaysRL, а в ней уже используется пакет DocRL.
Сначала вызывается процедура SMGenerateWaysRL, а в ней уже используется пакет DocRL.
SQL код:
PROCEDURE SMGenerateWaysRL( AID IN SMDOCUMENTS.ID%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
ALOCATION IN SMDOCUMENTS.LOCATION%TYPE,
ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE,
ADOCID_WI IN OUT SMDOCUMENTS.ID%TYPE,
ADOCID_WO IN OUT SMDOCUMENTS.ID%TYPE,
ADOCID_SR IN OUT SMDOCUMENTS.ID%TYPE,
ADOCID_WI3 IN OUT SMDOCUMENTS.ID%TYPE,
ADOCID_WO3 IN OUT SMDOCUMENTS.ID%TYPE,
ABLOCKFUTUREACTS IN INT,
AFAILSNUM OUT INT)
IS
VDATEBEFORE SMDOCUMENTS.CREATEDAT%TYPE;
BEGIN
IF ABLOCKFUTUREACTS = 1
THEN VDATEBEFORE := TO_DATE('99991231','YYYYMMDD');
ELSE VDATEBEFORE := ACREATEDAT;
END IF;
AFAILSNUM:=0;
CORE.CHECKUPDATE('RL', AID);
DELETE FROM TTDOCARTICLES;
INSERT INTO TTDOCARTICLES(ARTICLE, SUBARTICLE, QUANTITY)
SELECT R.ARTICLE, RL.SUBARTICLE, SUM(RL.QUANTITYDIFFERENCE)
FROM SVSPECSCALEIMPLRL RL, SMSPEC R, SMCARD C
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = AID
AND R.DOCTYPE = RL.DOCTYPE
AND R.DOCID = RL.DOCID
AND R.SPECITEM = RL.SPECITEM
AND RL.SUBARTICLE IS NOT NULL
AND RL.QUANTITYDIFFERENCE <> 0
AND C.ARTICLE = R.ARTICLE
AND C.DATATYPE <> 4
GROUP BY R.ARTICLE, RL.SUBARTICLE;
DOCRL.GENERATEWAYBILLSSPECRL(AID, ADOCID_WI, ADOCID_WI3, ACREATEDAT,
ALOCATION, ACLIENTINDEX, 1);
DOCRL.GENERATEWAYBILLSSPECRL(AID, ADOCID_WO, ADOCID_WO3, ACREATEDAT,
ALOCATION, ACLIENTINDEX, -1);
DOCRL.GENERATESORTACTSSPECRL(AID, ADOCID_SR, ACREATEDAT, ALOCATION, ACLIENTINDEX);
DOCRL.SMBLOCKACTSBYROLLS(AID, VDATEBEFORE, ALOCATION, AFAILSNUM);
END;
SQL код:
PACKAGE DocRL AS
SUBTYPE TDOCTYPE IS SMDOCUMENTS.DOCTYPE%TYPE;
SUBTYPE TDOCID IS SMDOCUMENTS.ID%TYPE;
SUBTYPE TIDASSORT IS SMDOCSASSORT.IDASSORT%TYPE;
SUBTYPE TDATE IS SMDOCUMENTS.CREATEDAT%TYPE;
SUBTYPE TLOCATION IS SMDOCUMENTS.LOCATION%TYPE;
SUBTYPE TCLIENTID IS SMDOCUMENTS.CLIENTINDEX%TYPE;
SUBTYPE TOPCODE IS SMDOCUMENTS.OPCODE%TYPE;
SUBTYPE TDOCSTATE IS SMDOCUMENTS.DOCSTATE%TYPE;
SUBTYPE TPRICEROUNDMODE IS SMDOCUMENTS.PRICEROUNDMODE%TYPE;
SUBTYPE TSPECITEM IS SMSPEC.SPECITEM%TYPE;
SUBTYPE TARTICLE IS SMSPEC.ARTICLE%TYPE;
SUBTYPE TMONEY IS SMSPEC.ITEMPRICE%TYPE;
SUBTYPE TSUBARTICLE IS SMSPECSCALE.SUBARTICLE%TYPE;
SUBTYPE TQUANTITY IS SMSPEC.QUANTITY%TYPE;
SUBTYPE TSTAFFTYPE IS SMDOCSTAFF.STAFFTYPE%TYPE;
SUBTYPE TSTAFFNAME IS SMDOCSTAFF.STAFFNAME%TYPE;
SUBTYPE TSTAFFPOSITION IS SMDOCSTAFF.STAFFPOSITION%TYPE;
SUBTYPE TZONEID IS SMROLLS.ZONEID%TYPE;
SUBTYPE TPRICETYPE IS SMROLLS.PRICETYPE%TYPE;
SUBTYPE TPROCESSID IS SMPROCESS.PROCESSID%TYPE;
PROCEDURE CREATEROLLS(AID IN SMDOCUMENTS.ID%TYPE,
ALOCATION IN SMDOCUMENTS.LOCATION%TYPE,
AROLLMODE IN SMROLLS.ROLLMODE%TYPE,
AFILLSPECTYPE IN SMROLLS.FILLSPECTYPE%TYPE,
AAUXID IN NUMBER,
APRICEMODE IN SMROLLS.PRICEMODE%TYPE,
APRICETYPE IN SMPRICETYPES.ID%TYPE,
AWITHDUE IN SMPRICETYPES.WITHDUE%TYPE,
AISFILLCOMPLETE IN SMROLLS.ISFILLCOMPLETE%TYPE,
AISACTIVEONLY IN SMROLLS.ISACTIVEONLY%TYPE,
AORDERNO IN SMROLLS.ORDERNO%TYPE,
AOURSELFCLIENT IN SMROLLS.OURSELFCLIENT%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
AZONEID IN SMROLLS.ZONEID%TYPE,
ACHAIRMANNAME IN TSTAFFNAME, ACHAIRMANPOSITION IN TSTAFFPOSITION,
ACOMMISSIONNAME1 IN TSTAFFNAME, ACOMMISSIONPOSITION1 IN TSTAFFPOSITION,
ACOMMISSIONNAME2 IN TSTAFFNAME, ACOMMISSIONPOSITION2 IN TSTAFFPOSITION,
ACOMMISSIONNAME3 IN TSTAFFNAME, ACOMMISSIONPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME1 IN TSTAFFNAME,ARESPONSIBLEPOSITION1 IN TSTAFFPOSITION,
ARESPONSIBLENAME2 IN TSTAFFNAME,ARESPONSIBLEPOSITION2 IN TSTAFFPOSITION,
ARESPONSIBLENAME3 IN TSTAFFNAME,ARESPONSIBLEPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME4 IN TSTAFFNAME,ARESPONSIBLEPOSITION4 IN TSTAFFPOSITION
);
PROCEDURE SMBLOCKACTSBYROLLS(AID IN TDOCID,
ADATEBEFORE IN TDATE,
ALOCATION IN TLOCATION,
AFAILSNUM OUT INT);
PROCEDURE SMBLOCKPRODACTSBYROLLS(AID IN TDOCID,
ADATEBEFORE IN TDATE,
ALOCATION IN TLOCATION,
AZONEID IN TZONEID,
AFAILSNUM OUT INT);
PROCEDURE GENERATEWAYBILLSSPECRL(ADOCID_RL IN TDOCID,
ADOCID_W IN OUT TDOCID,
ADOCID_W3 IN OUT TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
ACLIENTINDEX IN TCLIENTID,
AIN IN INT);
PROCEDURE GENERATESORTACTSSPECRL(ADOCID_RL IN TDOCID,
ADOCID_SR IN OUT TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
ACLIENTINDEX IN TCLIENTID);
PROCEDURE GETROLLSCHILDREN( ADOCID_RL IN SMDOCUMENTS.ID%TYPE,
ALOCATION IN SMDOCUMENTS.LOCATION%TYPE,
AWAYSIN IN INT,
ACHILDCOUNT OUT INT,
ADOCTYPE OUT SMDOCUMENTS.DOCTYPE%TYPE,
ADOCID OUT SMDOCUMENTS.ID%TYPE);
PROCEDURE REFRESHQTYSCALE( ADOCTYPE IN TDOCTYPE,
ADOCID IN TDOCID);
PROCEDURE GENERATEACTSSPECRL( ADOCID_RL IN TDOCID,
ADOCTYPE_A IN TDOCTYPE,
ADOCID_A IN OUT TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
AEXISTACTSMODE IN INT);
PROCEDURE GENERATEACTSSPECPRODRL(ADOCID_RL IN TDOCID,
ADOCTYPE_A IN TDOCTYPE,
ADOCID_A IN TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
AZONEID IN TZONEID,
AEXISTACTSMODE IN INT,
ACREATED OUT INT);
PROCEDURE CLEARQUANTITYRL(ADOCID IN SMDOCUMENTS.ID%TYPE);
PROCEDURE TOINGREDIENTSRL(ADOCID IN SMDOCUMENTS.ID%TYPE);
PROCEDURE LOADARTICLEPRODPRICES(ADATE IN TDATE,
ANEARESTDATE IN CORE.SMBOOL,
AZONEID IN TZONEID DEFAULT -2);
PROCEDURE LOADPRODPRICES(ADOCTYPE IN TDOCTYPE,
ADOCID IN TDOCID,
ALOCATIONID IN TLOCATION,
AZONEID IN TZONEID,
ADOCDATE IN TDATE,
ANEARESTDATE IN VARCHAR2,
AONLYFORZEROS IN VARCHAR2);
PROCEDURE SETALLPRICESRL(ADOCID IN TDOCID,
AZEROFORNULLPRICETYPE IN VARCHAR2,
ANEARESTDATE IN VARCHAR2,
AONLYFORZEROS IN VARCHAR2);
PROCEDURE CALCULATEROLLSQUANTITY(ADOCID IN SMDOCUMENTS.ID%TYPE,
AADDNOZERTQTY IN CORE.SMBOOL);
PROCEDURE SETPURCHASINGPRICE(AID IN TDOCID);
FUNCTION GETPURCHASINGSUM(
AID IN SMDOCUMENTS.ID%TYPE,
ASPECITEM IN SMSPEC.SPECITEM%TYPE,
AQUANTITY IN SMSPEC.QUANTITY%TYPE
) RETURN SMSPEC.TOTALPRICE%TYPE;
PROCEDURE UPDATEAWAITSUMMS(
AID IN SMSPEC.DOCID%TYPE,
ASPECITEM IN SMSPEC.SPECITEM%TYPE,
AAWAITTOTALPRICE IN SMSPECRL.AWAITTOTALPRICE%TYPE,
AAWAITTOTALPRICECUR IN SMSPECRL.AWAITTOTALPRICECUR%TYPE
);
PROCEDURE ADDSPEC(
AID IN SMSPEC.DOCID%TYPE,
AARTICLE IN SMSPEC.ARTICLE%TYPE,
ADISPLAYITEM IN SMSPEC.DISPLAYITEM%TYPE,
AQUANTITY IN SMSPEC.QUANTITY%TYPE,
AITEMPRICE IN SMSPEC.ITEMPRICE%TYPE,
AITEMPRICECUR IN SMSPEC.ITEMPRICECUR%TYPE,
ATOTALPRICE IN SMSPEC.TOTALPRICE%TYPE,
ATOTALPRICECUR IN SMSPEC.TOTALPRICECUR%TYPE
);
PROCEDURE UPDHEAD(
AID IN SMDOCUMENTS.ID%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
APREAMBLEDATE IN SMROLLS.PREAMBLEDATE%TYPE,
AFINALDATE IN SMROLLS.FINALDATE%TYPE,
AWITHDUE IN SMROLLS.WITHDUE%TYPE,
ACURRENCYTYPE IN SMDOCUMENTS.CURRENCYTYPE%TYPE,
ACURRENCYRATE IN SMDOCUMENTS.CURRENCYRATE%TYPE,
ACURRENCYMULTORDER IN SMDOCUMENTS.CURRENCYMULTORDER%TYPE,
ACOMMENTARY IN SMDOCUMENTS.COMMENTARY%TYPE
);
PROCEDURE UPDHEADDRAFT(
AID IN SMDOCUMENTS.ID%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
APREAMBLEDATE IN SMROLLS.PREAMBLEDATE%TYPE,
AFINALDATE IN SMROLLS.FINALDATE%TYPE,
AWITHDUE IN SMROLLS.WITHDUE%TYPE,
ACURRENCYTYPE IN SMDOCUMENTS.CURRENCYTYPE%TYPE,
ACURRENCYRATE IN SMDOCUMENTS.CURRENCYRATE%TYPE,
ACURRENCYMULTORDER IN SMDOCUMENTS.CURRENCYMULTORDER%TYPE,
ACOMMENTARY IN SMDOCUMENTS.COMMENTARY%TYPE,
ASTAFFCHANGED IN VARCHAR2,
ACHAIRMANNAME IN TSTAFFNAME, ACHAIRMANPOSITION IN TSTAFFPOSITION,
ACOMMISSIONNAME1 IN TSTAFFNAME, ACOMMISSIONPOSITION1 IN TSTAFFPOSITION,
ACOMMISSIONNAME2 IN TSTAFFNAME, ACOMMISSIONPOSITION2 IN TSTAFFPOSITION,
ACOMMISSIONNAME3 IN TSTAFFNAME, ACOMMISSIONPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME1 IN TSTAFFNAME,ARESPONSIBLEPOSITION1 IN TSTAFFPOSITION,
ARESPONSIBLENAME2 IN TSTAFFNAME,ARESPONSIBLEPOSITION2 IN TSTAFFPOSITION,
ARESPONSIBLENAME3 IN TSTAFFNAME,ARESPONSIBLEPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME4 IN TSTAFFNAME,ARESPONSIBLEPOSITION4 IN TSTAFFPOSITION
);
PROCEDURE SETPRICETYPE(
AID IN SMDOCUMENTS.ID%TYPE,
APRICEMODE IN SMROLLS.PRICEMODE%TYPE,
APRICETYPE IN TPRICETYPE
);
PROCEDURE GENERATEPRODSURPLUSWAYSRL(ADOCTYPE IN SMDOCUMENTS.DOCTYPE%TYPE
,ADOCID IN SMDOCUMENTS.ID%TYPE
,ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE
,ADOCID_WI IN SMDOCUMENTS.ID%TYPE
,ADOCID_PE IN SMDOCUMENTS.ID%TYPE
,ABLOCKFUTUREACTS IN INT
,AFAILSNUM OUT INT
,ACREATEDIN OUT INT);
PROCEDURE GENERATEPRODDEFICITWAYSRL(ADOCTYPE IN SMDOCUMENTS.DOCTYPE%TYPE
,ADOCID IN SMDOCUMENTS.ID%TYPE
,ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE
,ADOCID_PO IN SMDOCUMENTS.ID%TYPE
,ADOCID_WO IN SMDOCUMENTS.ID%TYPE
,ABLOCKFUTUREACTS IN INT
,AFAILSNUM OUT INT
,ACREATEDIN OUT INT);
PROCEDURE LOADDOCARTICLES(ALOCATION IN TLOCATION,
AFILLSPECTYPE IN INT,
AAUXID IN NUMBER,
AISFILLCOMPLETE IN VARCHAR2,
AISACTIVEONLY IN VARCHAR2
);
PROCEDURE CLOSEDEDITDATEAFTERINVENTORY(ADOCID IN TDOCID);
PROCEDURE CANCELEDITDATEAFTERINVENTORY(ADOCID IN TDOCID);
PROCEDURE GENERATEDOCSRL(AISACTS IN INT
,ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE
,AISJOINEXISTACTS IN INT
,AISBLOCKFUTUREACTS IN INT);
PROCEDURE CREATEDOCSBYOINV(APROCESSID IN TPROCESSID, AFAID OUT TDOCID, ALAID OUT TDOCID, AILID OUT TDOCID);
END DOCRL;
SQL код:
PACKAGE BODY DocRL AS
PROCEDURE ASSERT(CONDITION IN BOOLEAN, EXCEPTION_CODE IN NUMBER DEFAULT (-20000-11))
IS
BEGIN
IF NOT CONDITION THEN
CORE.FAIL(EXCEPTION_CODE);
END IF;
END;
FUNCTION GETWITHDUE(AID IN SMDOCUMENTS.ID%TYPE,
APRICEMODE IN SMROLLS.PRICEMODE%TYPE,
APRICETYPE IN SMPRICETYPES.ID%TYPE,
ALOCATION IN SMSTORELOCATIONS.ID%TYPE)
RETURN SMPRICETYPES.WITHDUE%TYPE
IS
VDUE SMPRICETYPES.WITHDUE%TYPE := '0';
BEGIN
IF APRICEMODE IN (1, 3)
THEN
VDUE := PRICES.ISCHARGEFULLPRICE(APRICETYPE, ALOCATION);
ELSIF APRICEMODE = 0
THEN
BEGIN
SELECT WITHDUE INTO VDUE
FROM SMPRICETYPES
WHERE ID = APRICETYPE;
EXCEPTION WHEN NO_DATA_FOUND THEN
VDUE:='0';
END;
END IF;
RETURN VDUE;
END;
PROCEDURE LOADDOCARTICLES(ALOCATION IN TLOCATION,
AFILLSPECTYPE IN INT,
AAUXID IN NUMBER,
AISFILLCOMPLETE IN VARCHAR2,
AISACTIVEONLY IN VARCHAR2)
IS
PROCEDURE ARTBYLOCATION
IS
BEGIN
INSERT INTO TTDOCARTICLES(ARTICLE)
SELECT ARTICLE FROM SMCARD C
WHERE
DATATYPE IN (0, 3, 4)
AND ( (AISACTIVEONLY='0') OR ACCEPTED = 1)
AND ( (AISFILLCOMPLETE='0')
OR EXISTS (SELECT * FROM SMGOODS G
WHERE G.ARTICLE = C.ARTICLE
AND G.STORELOC = ALOCATION
AND G.QUANTITY > 0)
);
END ARTBYLOCATION;
PROCEDURE ARTBYCARDCLASS
IS
BEGIN
INSERT INTO TTDOCARTICLES(ARTICLE)
SELECT ARTICLE FROM SMCARD C
WHERE
DATATYPE IN (0, 3, 4)
AND ( (AISACTIVEONLY='0') OR ACCEPTED = 1)
AND ( (AISFILLCOMPLETE='0')
OR EXISTS (SELECT * FROM SMGOODS G
WHERE G.ARTICLE = C.ARTICLE
AND G.STORELOC = ALOCATION
AND G.QUANTITY > 0)
)
AND (AAUXID = 0
OR EXISTS (SELECT 1 FROM SACARDCLASS CLS, SACARDCLASS P
WHERE CLS.ID = C.IDCLASS
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = AAUXID)
);
END ARTBYCARDCLASS;
PROCEDURE ARTBYASSORT
IS
BEGIN
INSERT INTO TTDOCARTICLES(ARTICLE)
SELECT C.ARTICLE FROM SMCARD C
WHERE
DATATYPE IN (0, 3, 4)
AND ( (AISACTIVEONLY='0') OR ACCEPTED = 1)
AND ( (AISFILLCOMPLETE='0')
OR EXISTS (SELECT * FROM SMGOODS G
WHERE G.ARTICLE = C.ARTICLE
AND G.STORELOC = ALOCATION
AND G.QUANTITY > 0)
)
AND (AAUXID = 0
OR EXISTS (SELECT 1 FROM SMCARDASSORT A, SACARDASSORT CLS, SACARDASSORT P
WHERE C.ARTICLE = A.ARTICLE
AND CLS.ID = A.IDASSORT
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = AAUXID)
);
END ARTBYASSORT;
PROCEDURE ARTBYMATRIX
IS
BEGIN
INSERT INTO TTDOCARTICLES(ARTICLE)
SELECT C.ARTICLE FROM SMCARD C
WHERE
DATATYPE IN (0, 3, 4)
AND ( (AISACTIVEONLY='0') OR ACCEPTED = 1)
AND ( (AISFILLCOMPLETE='0')
OR EXISTS (SELECT * FROM SMGOODS G
WHERE G.ARTICLE = C.ARTICLE
AND G.STORELOC = ALOCATION
AND G.QUANTITY > 0)
)
AND (AAUXID = 0
OR EXISTS (SELECT 1 FROM SMCARDMATRIX A, SACARDMATRIX CLS, SACARDMATRIX P
WHERE C.ARTICLE = A.ARTICLE
AND CLS.ID = A.IDMATRIX
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = AAUXID)
);
END ARTBYMATRIX;
PROCEDURE ARTBYDEPARTMENT
IS
BEGIN
INSERT INTO TTDOCARTICLES(ARTICLE)
SELECT ARTICLE FROM SMCARD C
WHERE
DATATYPE IN (0, 3, 4)
AND ( (AISACTIVEONLY='0') OR ACCEPTED = 1)
AND ( (AISFILLCOMPLETE='0')
OR EXISTS (SELECT * FROM SMGOODS G
WHERE G.ARTICLE = C.ARTICLE
AND G.STORELOC = ALOCATION
AND G.QUANTITY > 0)
)
AND (EXISTS (SELECT * FROM SMCASHDESKGOODS DEP
WHERE DEP.IDCLASS = 0
AND DEP.LOCID = ALOCATION
AND DEP.DEPID = AAUXID)
OR EXISTS (SELECT * FROM SACARDCLASS CLS, SACARDCLASS P, SMCASHDESKGOODS DEP
WHERE CLS.ID = C.IDCLASS
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = DEP.IDCLASS
AND DEP.LOCID = ALOCATION
AND DEP.DEPID = AAUXID)
OR EXISTS (SELECT * FROM SMCARDMATRIX M, SACARDMATRIX CLS, SACARDMATRIX P, SMCASHDESKGOODS DEP
WHERE M.ARTICLE = C.ARTICLE
AND CLS.ID = M.IDMATRIX
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = DEP.IDMATRIX
AND DEP.LOCID = ALOCATION
AND DEP.DEPID = AAUXID)
);
END;
PROCEDURE ARTBYDEPGROUP
IS
BEGIN
INSERT INTO TTDOCARTICLES(ARTICLE)
SELECT ARTICLE
FROM SMCARD C
WHERE
DATATYPE IN (0, 3, 4)
AND ( (AISACTIVEONLY='0') OR ACCEPTED = 1)
AND ( (AISFILLCOMPLETE='0')
OR EXISTS (SELECT * FROM SMGOODS G
WHERE G.ARTICLE = C.ARTICLE
AND G.STORELOC = ALOCATION
AND G.QUANTITY > 0)
)
AND (EXISTS (SELECT * FROM SMCASHDESKGOODS DEP, SMDEPARTMENTS DG
WHERE DEP.IDCLASS = 0
AND DEP.LOCID = DG.LOCID
AND DEP.DEPID = DG.DEPID
AND DG.LOCID = ALOCATION
AND DG.GROUPDEPID = AAUXID)
OR EXISTS (SELECT * FROM SACARDCLASS CLS, SACARDCLASS P, SMCASHDESKGOODS DEP, SMDEPARTMENTS DG
WHERE CLS.ID = C.IDCLASS
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = DEP.IDCLASS
AND DEP.LOCID = DG.LOCID
AND DEP.DEPID = DG.DEPID
AND DG.LOCID = ALOCATION
AND DG.GROUPDEPID = AAUXID)
OR EXISTS (SELECT * FROM SMCARDMATRIX M, SACARDMATRIX CLS, SACARDMATRIX P, SMCASHDESKGOODS DEP, SMDEPARTMENTS DG
WHERE M.ARTICLE = C.ARTICLE
AND CLS.ID = M.IDMATRIX
AND CLS.TREE LIKE P.TREE || '%'
AND P.ID = DEP.IDMATRIX
AND DEP.LOCID = DG.LOCID
AND DEP.DEPID = DG.DEPID
AND DG.LOCID = ALOCATION
AND DG.GROUPDEPID = AAUXID)
);
END;
BEGIN
DELETE FROM TTDOCARTICLES;
IF AFILLSPECTYPE = 1
THEN
ARTBYLOCATION;
ELSIF AFILLSPECTYPE = 4
THEN
ARTBYCARDCLASS;
ELSIF AFILLSPECTYPE = 6
THEN
ARTBYASSORT;
ELSIF AFILLSPECTYPE = 7
THEN
ARTBYMATRIX;
ELSIF AFILLSPECTYPE = 2
THEN
ARTBYDEPARTMENT;
ELSIF AFILLSPECTYPE = 3
THEN
ARTBYDEPGROUP;
ELSIF AFILLSPECTYPE <> 0
THEN
CORE.FAIL((-20000-11));
END IF;
END;
PROCEDURE CREATEROLLS(AID IN SMDOCUMENTS.ID%TYPE,
ALOCATION IN SMDOCUMENTS.LOCATION%TYPE,
AROLLMODE IN SMROLLS.ROLLMODE%TYPE,
AFILLSPECTYPE IN SMROLLS.FILLSPECTYPE%TYPE,
AAUXID IN NUMBER,
APRICEMODE IN SMROLLS.PRICEMODE%TYPE,
APRICETYPE IN SMPRICETYPES.ID%TYPE,
AWITHDUE IN SMPRICETYPES.WITHDUE%TYPE,
AISFILLCOMPLETE IN SMROLLS.ISFILLCOMPLETE%TYPE,
AISACTIVEONLY IN SMROLLS.ISACTIVEONLY%TYPE,
AORDERNO IN SMROLLS.ORDERNO%TYPE,
AOURSELFCLIENT IN SMROLLS.OURSELFCLIENT%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
AZONEID IN SMROLLS.ZONEID%TYPE,
ACHAIRMANNAME IN TSTAFFNAME, ACHAIRMANPOSITION IN TSTAFFPOSITION,
ACOMMISSIONNAME1 IN TSTAFFNAME, ACOMMISSIONPOSITION1 IN TSTAFFPOSITION,
ACOMMISSIONNAME2 IN TSTAFFNAME, ACOMMISSIONPOSITION2 IN TSTAFFPOSITION,
ACOMMISSIONNAME3 IN TSTAFFNAME, ACOMMISSIONPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME1 IN TSTAFFNAME,ARESPONSIBLEPOSITION1 IN TSTAFFPOSITION,
ARESPONSIBLENAME2 IN TSTAFFNAME,ARESPONSIBLEPOSITION2 IN TSTAFFPOSITION,
ARESPONSIBLENAME3 IN TSTAFFNAME,ARESPONSIBLEPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME4 IN TSTAFFNAME,ARESPONSIBLEPOSITION4 IN TSTAFFPOSITION
)
IS
VWITHDUE SMPRICETYPES.WITHDUE%TYPE:='1';
VSPECTYPENAME SMROLLS.SPECTYPENAME%TYPE;
VISFILLCOMPLETE SMROLLS.ISFILLCOMPLETE%TYPE := AISFILLCOMPLETE;
VISACTIVEONLY SMROLLS.ISACTIVEONLY%TYPE := AISACTIVEONLY;
PROCEDURE FILLSPECBYIL
IS
BEGIN
INSERT INTO SMSPEC (DOCTYPE, DOCID, SPECITEM, DISPLAYITEM,
ARTICLE, QUANTITY,
TOTALPRICE, TOTALPRICECUR,
ITEMPRICE, ITEMPRICECUR)
SELECT 'RL', AID, ROWNUM, ROWNUM,
ARTICLE, QUANTITY,
0,0,0,0
FROM
(
SELECT S.ARTICLE, SUM(S.QUANTITY) AS QUANTITY
FROM SVDOCUMENTSIL D, SMSPEC S
WHERE D.ORDERNO = AORDERNO
AND D.OURSELFCLIENT = AOURSELFCLIENT
AND D.DOCSTATE>=2
AND S.DOCTYPE=D.DOCTYPE AND S.DOCID=D.ID
GROUP BY S.ARTICLE
);
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM,
SUBARTICLE, QUANTITY)
SELECT 'RL', AID, MYSPEC.SPECITEM,
S.SUBARTICLE, SUM(S.QUANTITY)
FROM SVDOCUMENTSIL D, SMSPECSCALE S,SMSPEC MYSPEC
WHERE D.ORDERNO = AORDERNO
AND D.OURSELFCLIENT = AOURSELFCLIENT
AND D.DOCSTATE >= 2
AND S.DOCTYPE = D.DOCTYPE
AND S.DOCID = D.ID
AND S.SUBARTICLE IS NOT NULL
AND EXISTS (SELECT * FROM SMSPEC S2, SMSPEC MYSPEC2
WHERE S2.DOCTYPE = S.DOCTYPE
AND S2.DOCID = S.DOCID
AND S2.SPECITEM = S.SPECITEM
AND MYSPEC2.DOCTYPE = MYSPEC.DOCTYPE
AND MYSPEC2.DOCID = MYSPEC.DOCID
AND MYSPEC2.SPECITEM = MYSPEC.SPECITEM
AND S2.ARTICLE = MYSPEC2.ARTICLE)
AND MYSPEC.DOCTYPE = 'RL'
AND MYSPEC.DOCID = AID
GROUP BY MYSPEC.SPECITEM, S.SUBARTICLE;
FOR VREC IN (SELECT SS.SPECITEM, SUM(SS.QUANTITY) AS QUANTITY
FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = 'RL'
AND SS.DOCID = AID
GROUP BY SS.SPECITEM)
LOOP
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, QUANTITY)
SELECT 'RL', AID, RL.SPECITEM, NULL, RL.QUANTITY - VREC.QUANTITY
FROM SMSPEC RL
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = AID
AND RL.SPECITEM = VREC.SPECITEM;
END LOOP;
END;
PROCEDURE INSERTGOODS(AWITHOPER VARCHAR2)
IS
BEGIN
IF (AWITHOPER='0')
THEN
INSERT INTO SMSPECRL(DOCTYPE, DOCID, SPECITEM, AWAITQUANTITY, AWAITTOTALPRICE, AWAITTOTALPRICECUR)
SELECT 'RL', AID, SP.SPECITEM, NVL(G.QUANTITY,0), 0, 0
FROM SMSPEC SP, SMGOODS G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND SP.ARTICLE = G.ARTICLE(+)
AND G.STORELOC(+) = ALOCATION;
INSERT INTO SMSPECSCALERL(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, AWAITQUANTITY)
SELECT 'RL', AID, SP.SPECITEM, G.SUBARTICLE, G.QUANTITY
FROM SMSPEC SP, SMGOODSSCALE G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND SP.ARTICLE = G.ARTICLE
AND G.STORELOC = ALOCATION;
ELSE
INSERT INTO SMSPECRL(DOCTYPE, DOCID, SPECITEM, AWAITQUANTITY, AWAITTOTALPRICE, AWAITTOTALPRICECUR)
SELECT 'RL', AID, SPECITEM, SUM(QUANTITY), 0, 0
FROM
(SELECT SP.SPECITEM, NVL(G.QUANTITY,0) QUANTITY
FROM SMSPEC SP, SMGOODS G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND G.ARTICLE(+) = SP.ARTICLE
AND G.STORELOC(+) = ALOCATION
UNION ALL
SELECT SP.SPECITEM, (-G.SALEQUANTITY + G.RETURNQUANTITY) QUANTITY
FROM SMSPEC SP, SMOPERGOODS G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND G.ARTICLE = SP.ARTICLE
AND G.LOCID = ALOCATION
)
GROUP BY SPECITEM;
INSERT INTO SMSPECSCALERL(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, AWAITQUANTITY)
SELECT 'RL', AID, SPECITEM, SUBARTICLE, SUM(QUANTITY)
FROM
(SELECT SP.SPECITEM, G.SUBARTICLE, G.QUANTITY
FROM SMSPEC SP, SMGOODSSCALE G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND G.ARTICLE = SP.ARTICLE
AND G.STORELOC = ALOCATION
UNION ALL
SELECT SP.SPECITEM, G.SUBARTICLE, (-G.SALEQUANTITY + G.RETURNQUANTITY) QUANTITY
FROM SMSPEC SP, SMOPERGOODSSCALE G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND G.ARTICLE = SP.ARTICLE
AND G.LOCID = ALOCATION
)
GROUP BY SPECITEM, SUBARTICLE;
END IF;
FOR VREC IN (SELECT SS.SPECITEM, SUM(SS.AWAITQUANTITY) AS AWAITQUANTITY
FROM SMSPECSCALERL SS
WHERE SS.DOCTYPE = 'RL'
AND SS.DOCID = AID
GROUP BY SS.SPECITEM)
LOOP
INSERT INTO SMSPECSCALERL(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, AWAITQUANTITY)
SELECT 'RL', AID, RL.SPECITEM, NULL, RL.AWAITQUANTITY - VREC.AWAITQUANTITY
FROM SMSPECRL RL
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = AID
AND RL.SPECITEM = VREC.SPECITEM;
END LOOP;
END;
PROCEDURE INSERTPRODGOODS
IS
BEGIN
INSERT INTO SMSPECRL(DOCTYPE, DOCID, SPECITEM, AWAITQUANTITY, AWAITTOTALPRICE, AWAITTOTALPRICECUR)
SELECT 'RL', AID, SP.SPECITEM, NVL(G.QUANTITY,0), 0, 0
FROM SMSPEC SP, SMPRODGOODS G
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = AID
AND SP.ARTICLE = G.ARTICLE(+)
AND G.STORELOC(+) = ALOCATION
AND G.ZONEID(+)=AZONEID;
END;
PROCEDURE GETPRICEPARAMETERS
IS
VCURRENCY PRICES.TCURRRATEREC;
BEGIN
IF APRICETYPE IS NULL
THEN
VCURRENCY.IDCURRENCY := 0;
ELSE
BEGIN
SELECT CURRTYPE INTO VCURRENCY.IDCURRENCY
FROM SMPRICETYPES
WHERE ID = APRICETYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
VCURRENCY.IDCURRENCY := 0;
END;
END IF;
IF VCURRENCY.IDCURRENCY <> 0
THEN
VCURRENCY := PRICES.GETCURRRATE(VCURRENCY.IDCURRENCY, ACREATEDAT);
UPDATE SMDOCUMENTS
SET CURRENCYTYPE = VCURRENCY.IDCURRENCY,
CURRENCYRATE = VCURRENCY.SHOPRATE,
CURRENCYMULTORDER = VCURRENCY.MULTORDER,
ISROUBLES = '0'
WHERE ID = AID AND DOCTYPE = 'RL';
END IF;
END GETPRICEPARAMETERS;
BEGIN
CORE.CHECKUPDATE('RL', AID);
GETPRICEPARAMETERS;
IF AWITHDUE IS NOT NULL THEN
VWITHDUE := AWITHDUE;
ELSE
VWITHDUE := GETWITHDUE(AID, APRICEMODE, APRICETYPE, ALOCATION);
END IF;
IF AFILLSPECTYPE = 4
THEN
SELECT NAME INTO VSPECTYPENAME FROM SACARDCLASS WHERE ID = AAUXID;
ELSIF AFILLSPECTYPE = 6
THEN
SELECT NAME INTO VSPECTYPENAME FROM SACARDASSORT WHERE ID = AAUXID;
ELSIF AFILLSPECTYPE = 7
THEN
SELECT NAME INTO VSPECTYPENAME FROM SACARDMATRIX WHERE ID = AAUXID;
ELSIF AFILLSPECTYPE = 2
THEN
SELECT DEPNAME INTO VSPECTYPENAME FROM SMDEPARTMENTS WHERE DEPID = AAUXID AND LOCID = ALOCATION;
ELSIF AFILLSPECTYPE = 3
THEN
SELECT GROUPDEPNAME INTO VSPECTYPENAME FROM SMDEPARTMENTGROUPS WHERE GROUPDEPID = AAUXID AND LOCID = ALOCATION;
ELSIF AFILLSPECTYPE = 0
THEN
VISFILLCOMPLETE := '0';
VISACTIVEONLY := '0';
END IF;
INSERT INTO SMROLLS(ID, DOCTYPE, ROLLMODE, FILLSPECTYPE, SPECTYPENAME,
ISFILLCOMPLETE, ISACTIVEONLY,
PRICEMODE, PRICETYPE, ORDERNO, OURSELFCLIENT, PREAMBLEDATE, FINALDATE,
STORELOC, ZONEID, WITHDUE)
VALUES( AID, 'RL', AROLLMODE, AFILLSPECTYPE, VSPECTYPENAME,
VISFILLCOMPLETE, VISACTIVEONLY,
APRICEMODE, APRICETYPE, AORDERNO, AOURSELFCLIENT, ACREATEDAT, ACREATEDAT,
ALOCATION, AZONEID, VWITHDUE);
IF AFILLSPECTYPE = 5
THEN
DELETE FROM TTDOCARTICLES;
FILLSPECBYIL;
ELSE
DOCRL.LOADDOCARTICLES(ALOCATION, AFILLSPECTYPE, AAUXID, AISFILLCOMPLETE, AISACTIVEONLY);
END IF;
IF AFILLSPECTYPE != 0 AND AFILLSPECTYPE != 5
THEN
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM,
ARTICLE, QUANTITY, TOTALPRICE, TOTALPRICECUR,
ITEMPRICE, ITEMPRICECUR)
SELECT 'RL', AID, ROWNUM, ROWNUM,
ARTICLE, 0, 0, 0,
NULL, NULL
FROM TTDOCARTICLES;
IF AROLLMODE IN (0,2)
THEN
IF AROLLMODE = 2 THEN INSERTGOODS('1');
ELSIF AZONEID IS NULL THEN INSERTGOODS('0');
ELSE INSERTPRODGOODS;
END IF;
DOCUMENTS.SETDOCPROP('RL', AID, 'DocRL.Rest', '1');
IF APRICETYPE IS NOT NULL OR AZONEID IS NOT NULL THEN
SETALLPRICESRL(AID, '0', '0', '0');
END IF;
END IF;
END IF;
SMSETDOCSTAFF('RL', AID, 0, ACHAIRMANNAME, ACHAIRMANPOSITION);
SMSETDOCSTAFF('RL', AID, 1, ACOMMISSIONNAME1, ACOMMISSIONPOSITION1);
SMSETDOCSTAFF('RL', AID, 2, ACOMMISSIONNAME2, ACOMMISSIONPOSITION2);
SMSETDOCSTAFF('RL', AID, 3, ACOMMISSIONNAME3, ACOMMISSIONPOSITION3);
SMSETDOCSTAFF('RL', AID, 4, ARESPONSIBLENAME1, ARESPONSIBLEPOSITION1);
SMSETDOCSTAFF('RL', AID, 5, ARESPONSIBLENAME2, ARESPONSIBLEPOSITION2);
SMSETDOCSTAFF('RL', AID, 6, ARESPONSIBLENAME3, ARESPONSIBLEPOSITION3);
SMSETDOCSTAFF('RL', AID, 7, ARESPONSIBLENAME4, ARESPONSIBLEPOSITION4);
END;
PROCEDURE CLEARQUANTITYRL(ADOCID IN SMDOCUMENTS.ID%TYPE)
IS
BEGIN
CORE.CHECKUPDATE('RL', ADOCID);
UPDATE SMSPEC
SET QUANTITY=0,
TOTALPRICE=0,
TOTALPRICECUR=0
WHERE DOCTYPE='RL'
AND DOCID=ADOCID
AND QUANTITY<>0;
DELETE FROM SMSPECSCALE
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID;
END;
PROCEDURE TOINGREDIENTSRL(ADOCID IN SMDOCUMENTS.ID%TYPE)
IS
VLOCID TLOCATION;
VZONEID TZONEID;
VQTY TQUANTITY;
VCURRENCYPRECORDER SMCURRENCIES.PRECORDER%TYPE;
VCURRENCYMAINPRECORDER SMCURRENCIES.PRECORDER%TYPE;
BEGIN
BEGIN
SELECT D.LOCATION, D.ZONEID, C.PRECORDER
INTO VLOCID, VZONEID, VCURRENCYPRECORDER
FROM SVDOCUMENTSRL D, SMCURRENCIES C
WHERE D.ID = ADOCID AND C.ID = D.CURRENCYTYPE;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54),'RL'||'.'||ADOCID);
END;
IF VZONEID IS NULL THEN
CORE.FAILMSG('Производственный цех в сличительной ведомости '||ADOCID||' не задан.');
END IF;
SELECT PRECORDER INTO VCURRENCYMAINPRECORDER
FROM SMCURRENCIES WHERE ID = 0;
CORE.CHECKUPDATE('RL', ADOCID);
FOR VDST IN (SELECT S.SPECITEM, S.ARTICLE, S.QUANTITY,
I.ARTINGREDIENT, SS.SPECITEM SPECITEMINGREDIENT
FROM SMSPEC S, SVARTINGREDIENT I, SMSPEC SS
WHERE S.DOCTYPE = 'RL'
AND S.DOCID = ADOCID
AND S.QUANTITY <> 0
AND I.ARTICLE = S.ARTICLE
AND SS.DOCTYPE = 'RL'
AND SS.DOCID = ADOCID
AND SS.SPECITEM <> S.SPECITEM
AND SS.ARTICLE = I.ARTINGREDIENT
)
LOOP
VQTY := NVL(DOCPO.CALCALTQUANT(VLOCID, VDST.ARTICLE, VDST.ARTINGREDIENT, VDST.QUANTITY), 0);
UPDATE SMSPEC
SET QUANTITY = QUANTITY+VQTY,
TOTALPRICE = ROUND(ITEMPRICE * (QUANTITY+VQTY), -VCURRENCYMAINPRECORDER),
TOTALPRICENOTAX = ROUND(ITEMPRICE * (QUANTITY+VQTY), -VCURRENCYMAINPRECORDER),
TOTALPRICECUR = ROUND(ITEMPRICECUR * (QUANTITY+VQTY), -VCURRENCYPRECORDER)
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID
AND SPECITEM = VDST.SPECITEMINGREDIENT;
UPDATE SMSPEC
SET QUANTITY = 0,
TOTALPRICE = 0,
TOTALPRICENOTAX = 0,
TOTALPRICECUR = 0
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID
AND SPECITEM = VDST.SPECITEM;
DELETE FROM SMSPECSCALE
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID
AND SPECITEM = VDST.SPECITEM;
END LOOP;
END;
PROCEDURE GW_UPDATESUBARTICLEQTY(AARTICLE IN TARTICLE,
ASUBARTICLE IN TSUBARTICLE,
AABSQTY IN TQUANTITY)
IS
BEGIN
IF AABSQTY <= 0
THEN
DELETE FROM TTDOCARTICLES
WHERE ARTICLE = AARTICLE AND SUBARTICLE = ASUBARTICLE;
ELSE
UPDATE TTDOCARTICLES SET QUANTITY = DECODE(SIGN(QUANTITY), 1, AABSQTY, -AABSQTY)
WHERE ARTICLE = AARTICLE AND SUBARTICLE = ASUBARTICLE;
END IF;
END;
PROCEDURE GENERATESORTACTSSPECRL(ADOCID_RL IN TDOCID,
ADOCID_SR IN OUT TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
ACLIENTINDEX IN TCLIENTID)
IS
VPRICETYPE SMSORTACTS.PRICETYPE%TYPE;
VITEM INT := 1;
VITEMPRICE TMONEY;
VARTICLE TARTICLE;
PROCEDURE GW_CREATEHEADERSR
IS
BEGIN
BEGIN
SELECT PRICEID INTO VPRICETYPE
FROM SVAACCPRICE
WHERE STORELOC = ALOCATION AND CURRTYPE = 0;
EXCEPTION WHEN NO_DATA_FOUND THEN
VPRICETYPE := NULL;
END;
DOCUMENTS.CREATEDOCUMENTPROLOGUE('SR', ADOCID_SR, ACREATEDAT,
ACLIENTINDEX, ALOCATION, NULL, NULL,
1, '1', 36);
INSERT INTO SMSORTACTS(DOCTYPE, ID, PRICETYPE)
VALUES ('SR', ADOCID_SR, VPRICETYPE);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES('SR', ADOCID_SR, 'RL', ADOCID_RL);
END;
PROCEDURE GW_ADDSPECSR(AARTICLE IN TARTICLE,
ASUBARTICLEOLD IN TSUBARTICLE,
ASUBARTICLENEW IN TSUBARTICLE,
AQUANTITY IN TQUANTITY)
IS
BEGIN
INSERT INTO SMSPECSR(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
SUBARTICLEOLD, ITEMPRICEOLD, TOTALPRICEOLD,
SUBARTICLENEW, ITEMPRICENEW, TOTALPRICENEW)
VALUES('SR', ADOCID_SR, VITEM, VITEM, AARTICLE, AQUANTITY,
ASUBARTICLEOLD, VITEMPRICE, AQUANTITY * VITEMPRICE,
ASUBARTICLENEW, VITEMPRICE, AQUANTITY * VITEMPRICE);
VITEM := VITEM + 1;
END;
PROCEDURE GW_FILLSPECSR(AARTICLE IN TARTICLE,
ASUBARTICLE IN TSUBARTICLE,
AQUANTITY IN TQUANTITY)
IS
VCURRENTQTY TQUANTITY := ABS(AQUANTITY);
VSRQTY TQUANTITY;
VSUBARTICLENEW TSUBARTICLE;
VSUBARTICLEOLD TSUBARTICLE;
BEGIN
FOR VOTHER IN (SELECT SUBARTICLE, ABS(QUANTITY) QUANTITY
FROM TTDOCARTICLES
WHERE ARTICLE = AARTICLE
AND ((AQUANTITY < 0 AND QUANTITY > 0)
OR (AQUANTITY > 0 AND QUANTITY < 0))
ORDER BY ABS(QUANTITY))
LOOP
IF AQUANTITY < 0 THEN
VSUBARTICLEOLD := ASUBARTICLE;
VSUBARTICLENEW := VOTHER.SUBARTICLE;
ELSE
VSUBARTICLEOLD := VOTHER.SUBARTICLE;
VSUBARTICLENEW := ASUBARTICLE;
END IF;
VSRQTY := LEAST(VCURRENTQTY, VOTHER.QUANTITY);
GW_ADDSPECSR(AARTICLE, VSUBARTICLEOLD, VSUBARTICLENEW, VSRQTY);
VCURRENTQTY := VCURRENTQTY - VSRQTY;
GW_UPDATESUBARTICLEQTY(AARTICLE, ASUBARTICLE, VCURRENTQTY);
VOTHER.QUANTITY := VOTHER.QUANTITY - VSRQTY;
GW_UPDATESUBARTICLEQTY(AARTICLE, VOTHER.SUBARTICLE, VOTHER.QUANTITY);
IF VCURRENTQTY <= 0
THEN
RETURN;
END IF;
END LOOP;
IF AQUANTITY < 0 THEN
VSUBARTICLEOLD := ASUBARTICLE;
VSUBARTICLENEW := NULL;
ELSE
VSUBARTICLEOLD := NULL;
VSUBARTICLENEW := ASUBARTICLE;
END IF;
VSRQTY := VCURRENTQTY;
GW_ADDSPECSR(AARTICLE, VSUBARTICLEOLD, VSUBARTICLENEW, VSRQTY);
VCURRENTQTY := VCURRENTQTY - VSRQTY;
GW_UPDATESUBARTICLEQTY(AARTICLE, ASUBARTICLE, VCURRENTQTY);
END;
BEGIN
VITEM := 1;
LOOP
BEGIN
SELECT ARTICLE INTO VARTICLE FROM TTDOCARTICLES WHERE ROWNUM = 1;
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;
IF VITEM = 1 THEN
GW_CREATEHEADERSR;
END IF;
VITEMPRICE := DOCUMENTS.GETARTICLEPRICEONDATE(VARTICLE,VPRICETYPE,ALOCATION,ACREATEDAT);
FOR VREC IN (SELECT SUBARTICLE, QUANTITY
FROM TTDOCARTICLES
WHERE ARTICLE = VARTICLE AND QUANTITY < 0
ORDER BY ABS(QUANTITY))
LOOP
GW_FILLSPECSR(VARTICLE, VREC.SUBARTICLE, VREC.QUANTITY);
END LOOP;
FOR VREC IN (SELECT SUBARTICLE, QUANTITY
FROM TTDOCARTICLES
WHERE ARTICLE = VARTICLE AND QUANTITY > 0
ORDER BY ABS(QUANTITY))
LOOP
GW_FILLSPECSR(VARTICLE, VREC.SUBARTICLE, VREC.QUANTITY);
END LOOP;
DELETE FROM TTDOCARTICLES WHERE ARTICLE = VARTICLE;
END LOOP;
IF VITEM = 1 THEN
ADOCID_SR := NULL;
ELSE
DOCUMENTS.CHANGEDOCSTATE('SR', ADOCID_SR, 1, 2);
PRICECALCER.CALCDOC('SR', ADOCID_SR, NULL);
DOCUMENTS.UNLOCKDOC('SR', ADOCID_SR);
END IF;
END;
PROCEDURE GENERATEWAYBILLSSPECRL(ADOCID_RL IN TDOCID,
ADOCID_W IN OUT TDOCID,
ADOCID_W3 IN OUT TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
ACLIENTINDEX IN TCLIENTID,
AIN IN INT)
IS
VDOCTYPE_W TDOCTYPE;
VOURSELFCLIENT TCLIENTID;
VCURRENCYTYPE SMDOCUMENTS.CURRENCYTYPE%TYPE;
VCURRENCYMAINPRECORDER SMCURRENCIES.PRECORDER%TYPE;
VLOCATIONFROM TLOCATION;
VLOCATIONTO TLOCATION;
VOPCODE TOPCODE;
VWITHDUE SMROLLS.WITHDUE%TYPE;
VPRICEMODE SMROLLS.PRICEMODE%TYPE;
VISFULLPRICE CORE.SMBOOL := '1';
VSETSPECCAUSE CORE.SMBOOL := '0';
VISTOTALPRICE CORE.SMBOOL := '0';
PROCEDURE GW_CREATEDOC(PDOCTYPE IN TDOCTYPE, PDOCID IN TDOCID)
IS
BEGIN
DOCUMENTS.CREATEDOCUMENTPROLOGUE(PDOCTYPE, PDOCID,
ACREATEDAT, ACLIENTINDEX, NULL, VLOCATIONFROM, VLOCATIONTO);
UPDATE SMDOCUMENTS
SET OPCODE = VOPCODE,
USEROP = NULL,
CURRENCYTYPE = VCURRENCYTYPE,
PRICEROUNDMODE = DECODE(VISTOTALPRICE, '1',
DECODE(VISFULLPRICE, '1', 3, 4),
DECODE(VISFULLPRICE, '1', 1, 2))
WHERE DOCTYPE = PDOCTYPE AND ID = PDOCID;
IF AIN=1 THEN
INSERT INTO SMWAYBILLSIN( ID, GOODSOWNER, PAYCASH, OURSELFCLIENT)
VALUES( PDOCID, 0, '0', VOURSELFCLIENT);
ELSE
INSERT INTO SMWAYBILLSOUT( ID, PAYCASH, OURSELFCLIENT)
VALUES( PDOCID, '0', VOURSELFCLIENT);
END IF;
INSERT INTO SUPERMAG.SMCOMMONBASES( DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES(PDOCTYPE, PDOCID, 'RL', ADOCID_RL);
END;
FUNCTION GW_GETBASEPRICE(PSPECITEM IN TSPECITEM, PARTICLE IN TARTICLE)
RETURN SMSPEC.ITEMPRICE%TYPE
IS
BEGIN
FOR VBASE IN (SELECT SP.ITEMPRICE
FROM SMSPECRLBASES RL, SMSPEC SP
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = ADOCID_RL
AND RL.SPECITEM = PSPECITEM
AND SP.DOCTYPE = RL.BASEDOCTYPE
AND SP.DOCID = RL.BASEDOCID
AND SP.SPECITEM = RL.BASESPECITEM
AND SP.ARTICLE = PARTICLE
ORDER BY RL.ORDNUM DESC)
LOOP
RETURN VBASE.ITEMPRICE;
END LOOP;
RETURN 0;
END;
FUNCTION GW_FILLSPEC(PDOCTYPE IN TDOCTYPE, PDOCID IN TDOCID, PISTRANSPORT IN VARCHAR2)
RETURN BOOLEAN
IS
VITEM INT := 1;
BEGIN
FOR VREC IN (SELECT RL.ARTICLE, RL.SPECITEM,
AIN * RL.QUANTITYDIFFERENCE QUANTITY,
RL.ITEMPRICE
FROM SVSPECRL RL, SMCARD C
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = ADOCID_RL
AND (AIN * RL.QUANTITYDIFFERENCE) > 0
AND C.ARTICLE = RL.ARTICLE
AND ( ((PISTRANSPORT='0') AND C.DATATYPE <> 4)
OR ((PISTRANSPORT<>'0') AND C.DATATYPE = 4)
)
)
LOOP
IF VITEM = 1 THEN
GW_CREATEDOC(PDOCTYPE, PDOCID);
END IF;
IF (VSETSPECCAUSE<>'0') AND VDOCTYPE_W = 'WO'
THEN
FOR VBASE IN (SELECT RL.BASEDOCTYPE, RL.BASEDOCID, RL.BASESPECITEM,
RL.QUANTITY, SP.ITEMPRICE, SP.TOTALPRICE / SP.QUANTITY AS TOTALPRICEFORONE
FROM SMSPECRLBASES RL, SMSPEC SP
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = ADOCID_RL
AND RL.SPECITEM = VREC.SPECITEM
AND SP.DOCTYPE = RL.BASEDOCTYPE
AND SP.DOCID = RL.BASEDOCID
AND SP.SPECITEM = RL.BASESPECITEM
AND SP.ARTICLE = VREC.ARTICLE
AND RL.QUANTITY > 0
AND SP.QUANTITY > 0
ORDER BY RL.ORDNUM)
LOOP
IF VBASE.QUANTITY >= VREC.QUANTITY
THEN
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
CAUSETYPE, CAUSEID, CAUSESPECITEM,
ITEMPRICE, ITEMPRICENOTAX, ITEMPRICECUR,
TOTALPRICE, TOTALPRICENOTAX, TOTALPRICECUR
)
VALUES(PDOCTYPE, PDOCID, VITEM, VITEM, VREC.ARTICLE, VREC.QUANTITY,
VBASE.BASEDOCTYPE, VBASE.BASEDOCID, VBASE.BASESPECITEM,
VBASE.ITEMPRICE, 0, 0,
ROUND(VBASE.TOTALPRICEFORONE * VREC.QUANTITY, VCURRENCYMAINPRECORDER), 0, 0
);
VREC.QUANTITY := 0;
VITEM := VITEM + 1;
EXIT;
ELSE
VREC.QUANTITY := VREC.QUANTITY - VBASE.QUANTITY;
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
CAUSETYPE, CAUSEID, CAUSESPECITEM,
ITEMPRICE, ITEMPRICENOTAX, ITEMPRICECUR,
TOTALPRICE, TOTALPRICENOTAX, TOTALPRICECUR
)
VALUES(PDOCTYPE, PDOCID, VITEM, VITEM, VREC.ARTICLE, VBASE.QUANTITY,
VBASE.BASEDOCTYPE, VBASE.BASEDOCID, VBASE.BASESPECITEM,
VBASE.ITEMPRICE, 0, 0,
ROUND(VBASE.TOTALPRICEFORONE * VBASE.QUANTITY, VCURRENCYMAINPRECORDER), 0, 0
);
VITEM := VITEM + 1;
END IF;
END LOOP;
IF VREC.QUANTITY > 0
THEN
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
ITEMPRICE, ITEMPRICENOTAX, ITEMPRICECUR, TOTALPRICE, TOTALPRICECUR, TOTALPRICENOTAX)
VALUES(PDOCTYPE, PDOCID, VITEM, VITEM, VREC.ARTICLE, VREC.QUANTITY,
0, 0, 0, 0, 0, 0);
VITEM := VITEM + 1;
END IF;
ELSE
IF (VSETSPECCAUSE<>'0')
THEN
ASSERT(VDOCTYPE_W = 'WI');
VREC.ITEMPRICE := GW_GETBASEPRICE(VREC.SPECITEM, VREC.ARTICLE);
END IF;
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
ITEMPRICE, ITEMPRICENOTAX, ITEMPRICECUR,
TOTALPRICE, TOTALPRICENOTAX, TOTALPRICECUR)
VALUES(PDOCTYPE, PDOCID, VITEM, VITEM, VREC.ARTICLE, VREC.QUANTITY,
DECODE(VISFULLPRICE, '1', NVL(VREC.ITEMPRICE,0), 0),
DECODE(VISFULLPRICE, '1', 0, NVL(VREC.ITEMPRICE,0)),
0,
ROUND(DECODE(VISFULLPRICE, '1', NVL(VREC.ITEMPRICE,0), 0) * VREC.QUANTITY, VCURRENCYMAINPRECORDER),
ROUND(DECODE(VISFULLPRICE, '1', 0, NVL(VREC.ITEMPRICE,0)) * VREC.QUANTITY, VCURRENCYMAINPRECORDER),
0
);
VITEM := VITEM + 1;
END IF;
END LOOP;
RETURN VITEM > 1;
END;
PROCEDURE GW_UPDATENOSCALEQTY(PDOCTYPE IN TDOCTYPE, PDOCID IN TDOCID)
IS
BEGIN
FOR VREC IN (SELECT SS.SPECITEM, SUM(SS.QUANTITY) AS QUANTITY
FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = PDOCTYPE
AND SS.DOCID = PDOCID
GROUP BY SS.SPECITEM)
LOOP
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, QUANTITY)
SELECT PDOCTYPE, PDOCID, SP.SPECITEM, NULL, SP.QUANTITY-VREC.QUANTITY
FROM SMSPEC SP
WHERE SP.DOCTYPE = PDOCTYPE
AND SP.DOCID = PDOCID
AND SP.SPECITEM = VREC.SPECITEM;
END LOOP;
END;
PROCEDURE GW_FILLSPECSCALE(ADOCTYPE_W IN TDOCTYPE,
ADOCID_W IN TDOCID)
IS
BEGIN
FOR VREC IN (SELECT ARTICLE, SUBARTICLE, (AIN * QUANTITY) QUANTITY
FROM TTDOCARTICLES
WHERE (AIN * QUANTITY) > 0
ORDER BY ABS(QUANTITY) DESC
)
LOOP
FOR VNEW IN (SELECT SP.SPECITEM,
SP.QUANTITY - SUM(NVL(SC.QUANTITY, 0)) FREEQTY
FROM SMSPEC SP, SMSPECSCALE SC
WHERE SP.DOCTYPE = ADOCTYPE_W
AND SP.DOCID = ADOCID_W
AND SP.ARTICLE = VREC.ARTICLE
AND SC.DOCTYPE(+) = SP.DOCTYPE
AND SC.DOCID(+) = SP.DOCID
AND SC.SPECITEM(+) = SP.SPECITEM
GROUP BY SP.SPECITEM, SP.QUANTITY
HAVING SP.QUANTITY - SUM(NVL(SC.QUANTITY, 0)) > 0
)
LOOP
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, QUANTITY)
VALUES(ADOCTYPE_W, ADOCID_W, VNEW.SPECITEM, VREC.SUBARTICLE,
LEAST(VREC.QUANTITY, VNEW.FREEQTY));
VREC.QUANTITY := VREC.QUANTITY - VNEW.FREEQTY;
GW_UPDATESUBARTICLEQTY(VREC.ARTICLE, VREC.SUBARTICLE, VREC.QUANTITY);
IF VREC.QUANTITY <= 0
THEN
EXIT;
END IF;
END LOOP;
END LOOP;
GW_UPDATENOSCALEQTY(ADOCTYPE_W, ADOCID_W);
END;
BEGIN
IF ADOCID_W = ADOCID_W3
THEN
CORE.FAILMSG('Номера создаваемых накладных не должны совпадать');
END IF;
IF AIN=1 THEN
VDOCTYPE_W := 'WI';
VLOCATIONFROM := NULL;
VLOCATIONTO := ALOCATION;
ELSE
VDOCTYPE_W := 'WO';
VLOCATIONTO := NULL;
VLOCATIONFROM := ALOCATION;
END IF;
SELECT CURRENCYTYPE, OURSELFCLIENT, WITHDUE, PRICEMODE
INTO VCURRENCYTYPE, VOURSELFCLIENT, VWITHDUE, VPRICEMODE
FROM SVDOCUMENTSRL
WHERE ID = ADOCID_RL;
SELECT -PRECORDER INTO VCURRENCYMAINPRECORDER
FROM SMCURRENCIES
WHERE ID = 0;
IF VPRICEMODE = 3 AND (VWITHDUE='0')
THEN
VISFULLPRICE := '0';
END IF;
SELECT DECODE(COUNT(*), 0, '0', '1') INTO VSETSPECCAUSE
FROM DUAL
WHERE EXISTS (SELECT * FROM SMROLLS
WHERE DOCTYPE = 'RL'
AND ID = ADOCID_RL
AND PRICEMODE = 1)
AND EXISTS (SELECT * FROM SMSPECRLBASES
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID_RL);
IF (VSETSPECCAUSE<>'0')
THEN
SELECT DECODE(COUNT(*), 0, '0', '1') INTO VISTOTALPRICE
FROM DUAL
WHERE EXISTS (SELECT * FROM SMSPECRLBASES B, SMDOCUMENTS D
WHERE B.DOCTYPE = 'RL'
AND B.DOCID = ADOCID_RL
AND D.DOCTYPE = B.BASEDOCTYPE
AND D.ID = B.BASEDOCID
AND D.PRICEROUNDMODE IN (3, 4));
END IF;
IF AIN=1 THEN VOPCODE := 9;
ELSE VOPCODE := 8;
END IF;
IF GW_FILLSPEC(VDOCTYPE_W, ADOCID_W, '0')
THEN
GW_FILLSPECSCALE(VDOCTYPE_W, ADOCID_W);
DOCUMENTS.CHANGEDOCSTATE(VDOCTYPE_W, ADOCID_W, 1, 2);
PRICECALCER.CALCDOC(VDOCTYPE_W, ADOCID_W, NULL);
IF VPRICEMODE = 3
THEN
DOCUMENTS.CHANGEDOCSTATE(VDOCTYPE_W, ADOCID_W, 2, 3);
ELSIF VPRICEMODE = 1
THEN
DECLARE
VISINCOMEPRICE CORE.SMBOOL;
BEGIN
SELECT DECODE(COUNT(*), 0, '0', '1') INTO VISINCOMEPRICE
FROM DUAL
WHERE EXISTS (SELECT * FROM SMSPECRLBASES WHERE DOCTYPE = 'RL' AND DOCID = ADOCID_RL);
IF (VISINCOMEPRICE<>'0')
THEN
DOCUMENTS.CHANGEDOCSTATE(VDOCTYPE_W, ADOCID_W, 2, 3);
END IF;
END;
END IF;
DOCUMENTS.UNLOCKDOC(VDOCTYPE_W, ADOCID_W);
ELSE
ADOCID_W := NULL;
END IF;
IF AIN=1 THEN VOPCODE := 16;
ELSE VOPCODE := 17;
END IF;
IF GW_FILLSPEC(VDOCTYPE_W, ADOCID_W3, '1')
THEN
DOCUMENTS.CHANGEDOCSTATE(VDOCTYPE_W, ADOCID_W3, 1, 2);
PRICECALCER.CALCDOC(VDOCTYPE_W, ADOCID_W3, NULL);
DOCUMENTS.UNLOCKDOC(VDOCTYPE_W, ADOCID_W3);
ELSE
ADOCID_W3 := NULL;
END IF;
END;
PROCEDURE GETROLLSCHILDREN( ADOCID_RL IN SMDOCUMENTS.ID%TYPE,
ALOCATION IN SMDOCUMENTS.LOCATION%TYPE,
AWAYSIN IN INT,
ACHILDCOUNT OUT INT,
ADOCTYPE OUT SMDOCUMENTS.DOCTYPE%TYPE,
ADOCID OUT SMDOCUMENTS.ID%TYPE)
IS
VSELECT VARCHAR2(4000);
VDOCTYPEQUERY VARCHAR2(200);
CURSOR CWAYSIN IS
SELECT D.ID, D.DOCTYPE, DECODE(D.LOCATION,NULL,D.LOCATIONTO,D.LOCATION) LOCATION
FROM SMDOCUMENTS D, SMCOMMONBASES B
WHERE D.ID=B.ID AND D.DOCTYPE=B.DOCTYPE AND
D.DOCTYPE IN ('WI','FA') AND
D.DOCSTATE>=2 AND
B.BASEDOCTYPE='RL' AND B.BASEID=ADOCID_RL;
CURSOR CWAYSOUT IS
SELECT D.ID, D.DOCTYPE, DECODE(D.LOCATION,NULL,D.LOCATIONFROM,D.LOCATION) LOCATION
FROM SMDOCUMENTS D, SMCOMMONBASES B
WHERE D.ID=B.ID AND D.DOCTYPE=B.DOCTYPE AND
D.DOCTYPE IN ('WO','LA') AND
D.DOCSTATE>=2 AND
B.BASEDOCTYPE='RL' AND B.BASEID=ADOCID_RL;
VWAY CWAYSOUT%ROWTYPE;
VBADLOCATIONNUM INT:=0;
BEGIN
ACHILDCOUNT:=0;
IF AWAYSIN=0 THEN
OPEN CWAYSOUT;
ELSE
OPEN CWAYSIN;
END IF;
LOOP
IF AWAYSIN=0 THEN
FETCH CWAYSOUT INTO VWAY;
EXIT WHEN CWAYSOUT%NOTFOUND;
ELSE
FETCH CWAYSIN INTO VWAY;
EXIT WHEN CWAYSIN%NOTFOUND;
END IF;
IF VWAY.LOCATION=ALOCATION THEN
ACHILDCOUNT:=ACHILDCOUNT+1;
IF ACHILDCOUNT=1 THEN
ADOCID :=VWAY.ID;
ADOCTYPE:=VWAY.DOCTYPE;
END IF;
ELSE
VBADLOCATIONNUM:=VBADLOCATIONNUM+1;
END IF;
END LOOP;
IF AWAYSIN=0 THEN
CLOSE CWAYSOUT;
ELSE
CLOSE CWAYSIN;
END IF;
IF ACHILDCOUNT>0 THEN
IF ACHILDCOUNT>1 THEN
ADOCID :='';
ADOCTYPE:='';
END IF;
ELSE
IF VBADLOCATIONNUM>0 THEN ACHILDCOUNT:=-1; END IF;
END IF;
END;
PROCEDURE REFRESHQTYSCALE( ADOCTYPE IN TDOCTYPE,
ADOCID IN TDOCID)
IS
BEGIN
UPDATE SMSPEC S SET S.QUANTITY = (SELECT SUM(SS.QUANTITY) FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = ADOCTYPE
AND SS.DOCID = ADOCID
AND SS.SPECITEM = S.SPECITEM)
WHERE S.DOCTYPE = ADOCTYPE
AND S.DOCID = ADOCID
AND EXISTS (SELECT 1 FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = ADOCTYPE
AND SS.DOCID = ADOCID
AND SS.SPECITEM = S.SPECITEM);
DELETE FROM SMSPECSCALE S
WHERE S.DOCTYPE = ADOCTYPE
AND S.DOCID = ADOCID
AND S.SUBARTICLE IS NULL
AND NOT EXISTS (SELECT 1 FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = ADOCTYPE
AND SS.DOCID = ADOCID
AND SS.SPECITEM = S.SPECITEM
AND SS.SUBARTICLE IS NOT NULL
);
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, QUANTITY)
SELECT ADOCTYPE, ADOCID, S.SPECITEM, NULL, 0
FROM SMSPEC S
WHERE S.DOCTYPE = ADOCTYPE
AND S.DOCID = ADOCID
AND EXISTS (SELECT 1 FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = ADOCTYPE
AND SS.DOCID = ADOCID
AND SS.SPECITEM = S.SPECITEM
AND SS.SUBARTICLE IS NOT NULL
)
AND NOT EXISTS (SELECT 1 FROM SMSPECSCALE SS
WHERE SS.DOCTYPE = ADOCTYPE
AND SS.DOCID = ADOCID
AND SS.SPECITEM = S.SPECITEM
AND SS.SUBARTICLE IS NULL
);
END;
PROCEDURE GENERATEACTSSPECRL( ADOCID_RL IN TDOCID,
ADOCTYPE_A IN TDOCTYPE,
ADOCID_A IN OUT TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
AEXISTACTSMODE IN INT)
IS
VCOUNT INT;
VSIGN INT;
BEGIN
IF ADOCTYPE_A = 'FA'
THEN VSIGN := 1;
ELSE VSIGN := -1;
END IF;
SELECT COUNT(*) INTO VCOUNT FROM DUAL
WHERE EXISTS (SELECT 1 FROM SVSPECRL
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID_RL
AND VSIGN*DECODE(AEXISTACTSMODE,0,QUANTITYDIFFERENCE,ACTUALQUANTITYDIFFERENCE)>0
)
OR EXISTS (SELECT 1 FROM SVSPECSCALEIMPLRL
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID_RL
AND VSIGN*DECODE(AEXISTACTSMODE,0,QUANTITYDIFFERENCE,ACTUALQUANTITYDIFFERENCE)>0
);
IF VCOUNT = 0 THEN
ADOCID_A := NULL;
RETURN ;
END IF;
DOCUMENTS.CREATEDOCUMENTPROLOGUE(ADOCTYPE_A, ADOCID_A, ACREATEDAT, NULL, ALOCATION, NULL, NULL);
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
TOTALPRICE, TOTALPRICECUR, TOTALPRICENOTAX)
SELECT ADOCTYPE_A, ADOCID_A, SPECITEM, ROWNUM, ARTICLE, QUANTITY,
0, 0, 0
FROM (
SELECT S.SPECITEM, S.ARTICLE,
VSIGN*DECODE(AEXISTACTSMODE,0,S.QUANTITYDIFFERENCE,S.ACTUALQUANTITYDIFFERENCE) QUANTITY
FROM SVSPECRL S
WHERE S.DOCTYPE = 'RL'
AND S.DOCID = ADOCID_RL
AND VSIGN*DECODE(AEXISTACTSMODE,0,S.QUANTITYDIFFERENCE,S.ACTUALQUANTITYDIFFERENCE)>0
AND NOT EXISTS (SELECT 1 FROM SVSPECSCALEIMPLRL SS
WHERE SS.DOCTYPE = 'RL'
AND SS.DOCID = ADOCID_RL
AND SS.SPECITEM = S.SPECITEM
)
UNION ALL
SELECT S.SPECITEM, S.ARTICLE, 0 QUANTITY
FROM SVSPECRL S
WHERE S.DOCTYPE = 'RL'
AND S.DOCID = ADOCID_RL
AND EXISTS (SELECT 1 FROM SVSPECSCALEIMPLRL SS
WHERE SS.DOCTYPE = 'RL'
AND SS.DOCID = ADOCID_RL
AND SS.SPECITEM = S.SPECITEM
AND VSIGN*DECODE(AEXISTACTSMODE,0,SS.QUANTITYDIFFERENCE,SS.ACTUALQUANTITYDIFFERENCE)>0
)
);
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, QUANTITY)
SELECT ADOCTYPE_A, ADOCID_A, SS.SPECITEM, SS.SUBARTICLE,
VSIGN*DECODE(AEXISTACTSMODE,0,SS.QUANTITYDIFFERENCE,SS.ACTUALQUANTITYDIFFERENCE)
FROM SVSPECSCALEIMPLRL SS
WHERE SS.DOCTYPE = 'RL'
AND SS.DOCID = ADOCID_RL
AND VSIGN*DECODE(AEXISTACTSMODE,0,SS.QUANTITYDIFFERENCE,SS.ACTUALQUANTITYDIFFERENCE)>0;
REFRESHQTYSCALE(ADOCTYPE_A, ADOCID_A);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES(ADOCTYPE_A, ADOCID_A, 'RL', ADOCID_RL);
DOCUMENTS.CHANGEDOCSTATE(ADOCTYPE_A, ADOCID_A, 1, 2);
DOCUMENTS.UNLOCKDOC(ADOCTYPE_A, ADOCID_A);
END;
PROCEDURE GENERATEACTSSPECPRODRL(ADOCID_RL IN TDOCID,
ADOCTYPE_A IN TDOCTYPE,
ADOCID_A IN TDOCID,
ACREATEDAT IN TDATE,
ALOCATION IN TLOCATION,
AZONEID IN TZONEID,
AEXISTACTSMODE IN INT,
ACREATED OUT INT)
IS
VCOUNT INT;
VSIGN INT;
BEGIN
ACREATED := 0;
IF ADOCTYPE_A = 'PF'
THEN VSIGN := 1;
ELSE VSIGN := -1;
END IF;
SELECT COUNT(*) INTO VCOUNT FROM DUAL
WHERE EXISTS (SELECT 1 FROM SVSPECRL
WHERE DOCTYPE = 'RL'
AND DOCID = ADOCID_RL
AND VSIGN*DECODE(AEXISTACTSMODE,0,QUANTITYDIFFERENCE,ACTUALQUANTITYDIFFERENCE)>0
);
IF VCOUNT = 0 THEN
RETURN ;
END IF;
DOCPO.CREATEDOCUMENTPW(ADOCID_A, ACREATEDAT, ALOCATION, AZONEID, ADOCTYPE_A);
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
TOTALPRICE, TOTALPRICECUR, TOTALPRICENOTAX)
SELECT ADOCTYPE_A, ADOCID_A, S.SPECITEM, ROWNUM, S.ARTICLE,
VSIGN*DECODE(AEXISTACTSMODE,0,S.QUANTITYDIFFERENCE,S.ACTUALQUANTITYDIFFERENCE),
0, 0, 0
FROM SVSPECRL S
WHERE S.DOCTYPE = 'RL'
AND S.DOCID = ADOCID_RL
AND VSIGN*DECODE(AEXISTACTSMODE,0,S.QUANTITYDIFFERENCE,S.ACTUALQUANTITYDIFFERENCE)>0;
ACREATED := 1;
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES(ADOCTYPE_A, ADOCID_A, 'RL', ADOCID_RL);
DOCUMENTS.CHANGEDOCSTATE(ADOCTYPE_A, ADOCID_A, 1, 2);
END;
PROCEDURE CALCULATEROLLSQUANTITY(ADOCID IN SMDOCUMENTS.ID%TYPE,
AADDNOZERTQTY IN CORE.SMBOOL)
IS
VCREATEDAT SMDOCUMENTS.CREATEDAT%TYPE;
VLOCATION SMDOCUMENTS.LOCATION%TYPE;
VISROUBLES SMDOCUMENTS.ISROUBLES%TYPE;
VZONEID SMROLLS.ZONEID%TYPE;
VFILLSPECTYPE SMROLLS.FILLSPECTYPE%TYPE;
VPRICETYPE SMROLLS.PRICETYPE%TYPE;
VQUANTITY SMSPEC.QUANTITY%TYPE;
VDISPLAYITEM SMSPEC.DISPLAYITEM%TYPE;
VSPECITEM SMSPEC.SPECITEM%TYPE;
VCURRENCYRATE SMCURRRATES.SHOPRATE%TYPE;
VCURRENCYMULTORDER SMCURRRATES.MULTORDER%TYPE;
VCURRENCYPRECORDER SMCURRENCIES.PRECORDER%TYPE;
VCURRENCYMAINPRECORDER SMCURRENCIES.PRECORDER%TYPE;
PROCEDURE SETPRICEFORNEWPOSITION
IS
VPRICE SMSPEC.ITEMPRICE%TYPE;
VPRICE2 SMSPEC.ITEMPRICE%TYPE;
CURSOR VSMSPEC IS
SELECT ARTICLE,SPECITEM FROM SMSPEC SP
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = ADOCID
AND SP.SPECITEM > VSPECITEM
FOR UPDATE;
BEGIN
IF VPRICETYPE IS NULL THEN
RETURN;
END IF;
FOR VREC IN VSMSPEC
LOOP
VPRICE := NVL( DOCUMENTS.GETARTICLEPRICEONDATE(VREC.ARTICLE,VPRICETYPE,VLOCATION,VCREATEDAT) , 0 );
IF VISROUBLES = '1' THEN
VPRICE := ROUND(VPRICE,-VCURRENCYMAINPRECORDER);
VPRICE2 := ROUND(VPRICE/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER),-VCURRENCYPRECORDER);
ELSE
VPRICE2 := ROUND(VPRICE,-VCURRENCYPRECORDER);
VPRICE := ROUND(VPRICE2*VCURRENCYRATE/POWER(10, VCURRENCYMULTORDER), -VCURRENCYMAINPRECORDER);
END IF;
UPDATE SMSPEC SET ITEMPRICE = VPRICE,
ITEMPRICENOTAX = VPRICE,
ITEMPRICECUR = VPRICE2
WHERE CURRENT OF VSMSPEC;
END LOOP;
END;
BEGIN
CORE.CHECKUPDATE('RL', ADOCID);
SELECT RL.LOCATION, RL.ZONEID, RL.CREATEDAT, RL.ISROUBLES,
RL.FILLSPECTYPE, RL.PRICETYPE,
RL.CURRENCYRATE, RL.CURRENCYMULTORDER, C.PRECORDER
INTO VLOCATION, VZONEID, VCREATEDAT, VISROUBLES,
VFILLSPECTYPE, VPRICETYPE,
VCURRENCYRATE, VCURRENCYMULTORDER, VCURRENCYPRECORDER
FROM SVDOCUMENTSRL RL, SMCURRENCIES C
WHERE RL.ID=ADOCID AND C.ID=RL.CURRENCYTYPE;
IF VFILLSPECTYPE <> 0 AND (AADDNOZERTQTY<>'0')
THEN
CORE.FAILMSG('Добавление артикулов в документ возможно только'||
' для режима заполнения спецификации "Произвольный список товаров"');
END IF;
SELECT PRECORDER INTO VCURRENCYMAINPRECORDER
FROM SMCURRENCIES
WHERE ID = 0;
DELETE FROM SMSPECRL WHERE DOCTYPE='RL' AND DOCID=ADOCID;
DELETE FROM SMSPECSCALERL WHERE DOCTYPE = 'RL' AND DOCID = ADOCID;
UPDATE SMSPEC
SET TOTALPRICE = 0,
TOTALPRICECUR = 0
WHERE DOCTYPE='RL' AND DOCID=ADOCID AND ITEMPRICE IS NULL;
DELETE FROM SMSPECRLBASES WHERE DOCTYPE = 'RL' AND DOCID = ADOCID;
IF (AADDNOZERTQTY<>'0')
THEN
SELECT NVL(MAX(SPECITEM),0), NVL(MAX(DISPLAYITEM),0)
INTO VSPECITEM, VDISPLAYITEM
FROM SMSPEC SP
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = ADOCID;
END IF;
SQL_FILTER.CLEAR_ALLFILTERS;
SQL_FILTER.ADD_LOCATION(VLOCATION);
IF (AADDNOZERTQTY='0')
THEN
SQL_FILTER.SET_DOC('RL',ADOCID);
END IF;
IF VZONEID IS NULL THEN
REMAINS.CALC(VCREATEDAT);
REMAINS.CALCFOUNDQUANTITY(VCREATEDAT);
REMAINS.CALCSCALE(VCREATEDAT);
REMAINS.CALCFOUNDQUANTITYSCALE(VCREATEDAT);
IF (AADDNOZERTQTY<>'0')
THEN
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE,
QUANTITY, TOTALPRICE, TOTALPRICENOTAX, TOTALPRICECUR)
SELECT 'RL', ADOCID, VSPECITEM + ROWNUM, VDISPLAYITEM + ROWNUM, ARTICLE,
0, 0, 0, 0
FROM
(
SELECT CRD.ARTICLE FROM SMCARD CRD
WHERE CRD.DATATYPE IN (0, 3, 4)
AND (EXISTS (SELECT 1 FROM TTREMAINS T WHERE T.ARTICLE = CRD.ARTICLE)
OR EXISTS (SELECT 1 FROM TTFOUNDQUANTITY T WHERE T.ARTICLE = CRD.ARTICLE)
OR EXISTS (SELECT 1 FROM TTREMAINSSCALE T WHERE T.ARTICLE = CRD.ARTICLE)
OR EXISTS (SELECT 1 FROM TTFOUNDQUANTITYSCALE T WHERE T.ARTICLE = CRD.ARTICLE)
)
MINUS
SELECT SP.ARTICLE FROM SMSPEC SP
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = ADOCID
);
IF SQL%ROWCOUNT > 0 THEN
SETPRICEFORNEWPOSITION;
END IF;
END IF;
INSERT INTO SMSPECRL(DOCTYPE, DOCID, SPECITEM, AWAITQUANTITY,
AWAITTOTALPRICE, AWAITTOTALPRICECUR,
ACTUALQUANTITY)
(
SELECT 'RL', ADOCID, SP.SPECITEM, NVL(TT.QUANTITY,0),
ROUND(NVL(SP.ITEMPRICE,0) * NVL(TT.QUANTITY,0), -VCURRENCYMAINPRECORDER),
ROUND(NVL(SP.ITEMPRICECUR,0) * NVL(TT.QUANTITY,0), -VCURRENCYPRECORDER),
NVL(TT.QUANTITY,0)+NVL(FQ.QUANTITY,0)
FROM SMSPEC SP, TTREMAINS TT, TTFOUNDQUANTITY FQ
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = ADOCID
AND SP.ARTICLE = TT.ARTICLE(+)
AND SP.ARTICLE = FQ.ARTICLE(+)
);
INSERT INTO SMSPECSCALERL(DOCTYPE, DOCID, SPECITEM, SUBARTICLE,
AWAITQUANTITY, ACTUALQUANTITY)
(
SELECT 'RL', ADOCID, SP.SPECITEM, TT.SUBARTICLE,
TT.QUANTITY, TT.QUANTITY+TT.FOUNDQUANTITY
FROM SMSPEC SP,
(SELECT ARTICLE, SUBARTICLE,
SUM(QUANTITY) QUANTITY, SUM(FOUNDQUANTITY) FOUNDQUANTITY
FROM ( SELECT ARTICLE, SUBARTICLE, QUANTITY, 0 FOUNDQUANTITY
FROM TTREMAINSSCALE
UNION ALL
SELECT ARTICLE, SUBARTICLE, 0 QUANTITY, QUANTITY FOUNDQUANTITY
FROM TTFOUNDQUANTITYSCALE
)
GROUP BY ARTICLE, SUBARTICLE
) TT
WHERE SP.DOCTYPE='RL'
AND SP.DOCID=ADOCID
AND SP.ARTICLE=TT.ARTICLE
);
ELSE
SQL_FILTER.ADD_ZONE(VZONEID);
REMAINS.CALCPROD(VCREATEDAT);
REMAINS.CALCFOUNDQUANTITYPROD(VCREATEDAT);
IF (AADDNOZERTQTY<>'0')
THEN
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE,
QUANTITY, TOTALPRICE, TOTALPRICENOTAX, TOTALPRICECUR)
SELECT 'RL', ADOCID, VSPECITEM + ROWNUM, VDISPLAYITEM + ROWNUM, ARTICLE,
0, 0, 0, 0
FROM
(
SELECT CRD.ARTICLE FROM SMCARD CRD
WHERE CRD.DATATYPE IN (0, 3, 4)
AND (EXISTS (SELECT 1 FROM TTREMAINSPROD T WHERE T.ARTICLE = CRD.ARTICLE)
OR EXISTS (SELECT 1 FROM TTFOUNDQUANTITYPROD T WHERE T.ARTICLE = CRD.ARTICLE)
)
MINUS
SELECT SP.ARTICLE FROM SMSPEC SP
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = ADOCID
);
IF SQL%ROWCOUNT > 0 THEN
SETPRICEFORNEWPOSITION;
END IF;
END IF;
INSERT INTO SMSPECRL(DOCTYPE, DOCID, SPECITEM, AWAITQUANTITY,
AWAITTOTALPRICE, AWAITTOTALPRICECUR,
ACTUALQUANTITY)
(
SELECT 'RL', ADOCID, SP.SPECITEM, NVL(TT.QUANTITY,0),
ROUND(NVL(SP.ITEMPRICE,0) * NVL(TT.QUANTITY,0), -VCURRENCYMAINPRECORDER),
ROUND(NVL(SP.ITEMPRICECUR,0) * NVL(TT.QUANTITY,0), -VCURRENCYPRECORDER),
NVL(TT.QUANTITY,0)+NVL(FQ.QUANTITY,0)
FROM SMSPEC SP, TTREMAINSPROD TT, TTFOUNDQUANTITYPROD FQ
WHERE SP.DOCTYPE = 'RL'
AND SP.DOCID = ADOCID
AND SP.ARTICLE = TT.ARTICLE(+)
AND SP.ARTICLE = FQ.ARTICLE(+)
);
END IF;
FOR VREC IN (SELECT SS.SPECITEM,
SUM(SS.AWAITQUANTITY) AS AWAITQUANTITY,
SUM(SS.ACTUALQUANTITY) AS ACTUALQUANTITY
FROM SMSPECSCALERL SS
WHERE SS.DOCTYPE = 'RL'
AND SS.DOCID = ADOCID
GROUP BY SS.SPECITEM)
LOOP
INSERT INTO SMSPECSCALERL(DOCTYPE, DOCID, SPECITEM, SUBARTICLE,
AWAITQUANTITY, ACTUALQUANTITY)
SELECT 'RL', ADOCID, RL.SPECITEM, NULL,
RL.AWAITQUANTITY - VREC.AWAITQUANTITY,
RL.ACTUALQUANTITY - VREC.ACTUALQUANTITY
FROM SMSPECRL RL
WHERE RL.DOCTYPE = 'RL'
AND RL.DOCID = ADOCID
AND RL.SPECITEM = VREC.SPECITEM;
END LOOP;
DOCUMENTS.SETDOCPROP('RL', ADOCID, 'DocRL.Rest', '1');
END CALCULATEROLLSQUANTITY;
PROCEDURE SMBLOCKACTSBYROLLS( AID IN TDOCID,
ADATEBEFORE IN TDATE,
ALOCATION IN TLOCATION,
AFAILSNUM OUT INT)
IS
VFILLSPECTYPE SMROLLS.FILLSPECTYPE%TYPE;
TYPE TACTIDS IS TABLE OF TDOCID INDEX BY BINARY_INTEGER;
VACTIDS TACTIDS;
CURSOR VALLACTS(AACTTYPE TDOCTYPE)
IS
SELECT DOCTYPE, ID, DOCSTATE, CREATEDAT
FROM SMDOCUMENTS DA
WHERE DA.DOCTYPE = AACTTYPE
AND DA.DOCSTATE >= 2
AND DA.LOCATION = ALOCATION
AND DA.CREATEDAT <= ADATEBEFORE
AND EXISTS (SELECT 1 FROM SMSPEC SA, SMSPEC SD
WHERE SA.DOCID = DA.ID
AND SA.DOCTYPE = DA.DOCTYPE
AND SD.DOCTYPE = 'RL'
AND SD.DOCID = AID
AND SA.ARTICLE = SD.ARTICLE);
PROCEDURE UNLOCKACTS(AACTTYPE IN TDOCTYPE)
IS
BEGIN
IF VACTIDS.COUNT = 0 THEN RETURN; END IF;
FOR I IN VACTIDS.FIRST .. VACTIDS.LAST
LOOP
BEGIN
DOCUMENTS.UNLOCKDOC(AACTTYPE, VACTIDS(I));
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
PROCEDURE CREATEFOROTHERARTICLESACT(AACTTYPE IN TDOCTYPE,
AACTID IN TDOCID,
ACREATEDAT IN TDATE)
IS
VNEWACTID TDOCID;
BEGIN
FOR VACT IN (SELECT SPECITEM, ROWNUM DISPLAYITEM, ARTICLE, QUANTITY,
ITEMPRICE, TOTALPRICE, ITEMPRICENOTAX, TOTALPRICENOTAX, ITEMPRICECUR, TOTALPRICECUR
FROM SMSPEC A
WHERE A.DOCTYPE = AACTTYPE
AND A.DOCID = AACTID
AND NOT EXISTS (SELECT 1 FROM SMSPEC R
WHERE R.DOCTYPE = 'RL'
AND R.DOCID = AID
AND R.ARTICLE = A.ARTICLE))
LOOP
IF VNEWACTID IS NULL
THEN
VNEWACTID := SMGENERATEDOCNOROLLSWAYS(AID, AACTTYPE, ALOCATION);
VACTIDS(VACTIDS.COUNT+1) := VNEWACTID;
DOCUMENTS.CREATEDOCUMENTPROLOGUE(AACTTYPE, VNEWACTID, ACREATEDAT, NULL, ALOCATION, NULL, NULL);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES(AACTTYPE, VNEWACTID, 'RL', AID);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES(AACTTYPE, VNEWACTID, AACTTYPE, AACTID);
END IF;
INSERT INTO SMSPEC(DOCTYPE, DOCID, SPECITEM, DISPLAYITEM, ARTICLE, QUANTITY,
ITEMPRICE, TOTALPRICE, ITEMPRICENOTAX, TOTALPRICENOTAX, ITEMPRICECUR, TOTALPRICECUR)
VALUES(AACTTYPE, VNEWACTID, VACT.SPECITEM, VACT.DISPLAYITEM, VACT.ARTICLE, VACT.QUANTITY,
VACT.ITEMPRICE, VACT.TOTALPRICE, VACT.ITEMPRICENOTAX, VACT.TOTALPRICENOTAX, VACT.ITEMPRICECUR, VACT.TOTALPRICECUR);
INSERT INTO SMSPECSCALE(DOCTYPE, DOCID, SPECITEM, SUBARTICLE, QUANTITY)
SELECT AACTTYPE, VNEWACTID, SPECITEM, SUBARTICLE, QUANTITY
FROM SMSPECSCALE
WHERE DOCTYPE = AACTTYPE
AND DOCID = AACTID
AND SPECITEM = VACT.SPECITEM;
END LOOP;
IF VNEWACTID IS NOT NULL
THEN
DOCUMENTS.CHANGEDOCSTATE(AACTTYPE, VNEWACTID, 1, 2);
END IF;
END;
PROCEDURE BLOCKACTS(AACTTYPE IN TDOCTYPE)
IS
BEGIN
VACTIDS.DELETE;
FOR VACT IN VALLACTS(AACTTYPE)
LOOP
BEGIN
DOCUMENTS.LOCKDOC(VACT.DOCTYPE, VACT.ID);
EXCEPTION WHEN OTHERS THEN
AFAILSNUM := AFAILSNUM+1;
GOTO L_NEXTACT;
END;
BEGIN
FOR ASTATE IN REVERSE 1 .. VACT.DOCSTATE LOOP
DOCUMENTS.CHANGEDOCSTATE(VACT.DOCTYPE, VACT.ID, ASTATE, ASTATE-1);
END LOOP;
DOCLABELS.SETLABEL(VACT.DOCTYPE, VACT.ID, 'Sys.BlockByRL', AID, FALSE);
IF VFILLSPECTYPE <> 1
THEN
CREATEFOROTHERARTICLESACT(VACT.DOCTYPE, VACT.ID, VACT.CREATEDAT);
END IF;
DOCUMENTS.UNLOCKDOC(VACT.DOCTYPE, VACT.ID);
EXCEPTION WHEN OTHERS THEN
DOCUMENTS.UNLOCKDOC(VACT.DOCTYPE, VACT.ID);
UNLOCKACTS(AACTTYPE);
RAISE_APPLICATION_ERROR((-20000-12), 'Ошибка блокировки акта '||VACT.DOCTYPE||'.'||VACT.ID||': '||SQLERRM);
END;
<<L_NEXTACT>> NULL;
END LOOP;
UNLOCKACTS(AACTTYPE);
END;
BEGIN
AFAILSNUM := 0;
BEGIN
SELECT FILLSPECTYPE INTO VFILLSPECTYPE FROM SMROLLS WHERE ID = AID;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54), 'RL'||'.'||AID);
END;
BLOCKACTS('LA');
BLOCKACTS('FA');
END;
PROCEDURE SMBLOCKPRODACTSBYROLLS(AID IN TDOCID,
ADATEBEFORE IN TDATE,
ALOCATION IN TLOCATION,
AZONEID IN TZONEID,
AFAILSNUM OUT INT)
IS
CURSOR VALLACTS IS
SELECT DA.DOCTYPE, DA.ID, DA.DOCSTATE
FROM SMDOCUMENTS DA, SMDOCPROD R
WHERE DA.DOCTYPE IN ('PW', 'PF') AND
DA.DOCSTATE>=2 AND
DA.LOCATION=ALOCATION AND
DA.CREATEDAT<=ADATEBEFORE AND
EXISTS (SELECT * FROM SMSPEC SA, SMSPEC SD
WHERE SA.DOCID=DA.ID AND
SA.DOCTYPE=DA.DOCTYPE AND
SD.DOCTYPE='RL' AND
SD.DOCID=AID AND
SA.ARTICLE=SD.ARTICLE) AND
DA.ID=R.ID AND
DA.DOCTYPE=R.DOCTYPE AND
R.ZONEID=AZONEID;
BEGIN
AFAILSNUM:=0;
FOR VACT IN VALLACTS LOOP
BEGIN
DOCUMENTS.LOCKDOC(VACT.DOCTYPE, VACT.ID);
EXCEPTION WHEN OTHERS THEN
AFAILSNUM:=AFAILSNUM+1;
END;
BEGIN
FOR ASTATE IN REVERSE 1 .. VACT.DOCSTATE LOOP
DOCUMENTS.CHANGEDOCSTATE(VACT.DOCTYPE, VACT.ID, ASTATE, ASTATE-1);
END LOOP;
DOCLABELS.SETLABEL(VACT.DOCTYPE, VACT.ID, 'Sys.BlockByRL', AID, FALSE);
DOCUMENTS.UNLOCKDOC(VACT.DOCTYPE, VACT.ID);
EXCEPTION WHEN OTHERS THEN
DOCUMENTS.UNLOCKDOC(VACT.DOCTYPE, VACT.ID);
RAISE;
END;
END LOOP;
END;
PROCEDURE LOADARTICLEPRODPRICES(ADATE IN TDATE,
ANEARESTDATE IN CORE.SMBOOL,
AZONEID IN TZONEID DEFAULT -2)
IS
VSQL VARCHAR2(25000);
PROCEDURE LOADCALCULATION(ISNEARESTDATE IN CORE.SMBOOL)
IS
BEGIN
VSQL :=
'insert into TTProdPrice(LocID, Article, Price)'||
' select distinct Location, Article, Price'||
' from'||
' (select D.Location, S.Article,'||
'FIRST_VALUE(S.ItemPrice) over (partition by D.Location, S.Article order by ';
IF (ISNEARESTDATE<>'0') THEN
VSQL := VSQL||'D.Createdat, D.ID';
ELSE
VSQL := VSQL||'D.Createdat desc, D.ID desc';
END IF;
VSQL := VSQL || ', S.ItemPrice desc) Price'||
' from SMDocuments D, SMSpec S'||
' where D.DocType = S.DocType'||
' and D.ID = S.DocID'||
' and nvl(S.ItemPrice,0) <> 0'||
REPTOOLS.LOCSTRING(0,'D.Location')||
REPTOOLS.CLASSSTRING(4,'S')||
' and D.DocType = '''||'CA'||''''||
' and D.DocState = '||TO_CHAR(2);
IF (ISNEARESTDATE<>'0') THEN
VSQL := VSQL||' and D.CreatedAt > '||REP_DATE(ADATE);
ELSE
VSQL := VSQL||' and D.CreatedAt <= '||REP_DATE(ADATE);
END IF;
VSQL := VSQL||' and not exists (select 1 from TTProdPrice T where T.LocID = D.Location and T.Article = S.Article)'||
')';
EXECUTE IMMEDIATE VSQL;
END;
PROCEDURE LOADPRODEXPENSES(ISNEARESTDATE IN CORE.SMBOOL)
IS
BEGIN
VSQL :=
'insert into TTProdPrice(LocID, ProdZoneID, Article, Price)'||
' select distinct LocationFrom, ZoneID, Article, Price'||
' from'||
' (select D.LocationFrom, D.ZoneID, S.ArtIngredient Article,'||
'FIRST_VALUE(decode(S.AltQuantity,0,0,SP.TotalPrice/S.AltQuantity)) over (partition by D.LocationFrom, D.ZoneID, S.ArtIngredient order by ';
IF (ISNEARESTDATE<>'0') THEN
VSQL := VSQL||'D.Createdat, D.ID';
ELSE
VSQL := VSQL||'D.Createdat desc, D.ID desc';
END IF;
VSQL := VSQL || ', SP.ItemPrice desc) Price'||
' from SVDocumentsPE D, SMSpec SP, SMProdExpSpec S'||
' where D.DocType = S.DocType'||
' and D.ID = S.DocID'||
' and SP.DocType = S.DocType'||
' and SP.DocID = S.DocID'||
' and SP.SpecItem = S.SpecItem'||
' and nvl(SP.ItemPrice,0) <> 0'||
REPTOOLS.LOCSTRING(0,'D.LocationFrom')||
REPTOOLS.CLASSSTRING(4,'S.ArtIngredient')||
' and D.DocState = '||TO_CHAR(3);
IF (ISNEARESTDATE<>'0') THEN
VSQL := VSQL||' and D.CreatedAt > '||REP_DATE(ADATE);
ELSE
VSQL := VSQL||' and D.CreatedAt <= '||REP_DATE(ADATE);
END IF;
IF AZONEID <> -2 THEN
VSQL := VSQL||' and D.ZoneID = '||TO_CHAR(AZONEID);
END IF;
VSQL := VSQL||
' and not exists (select 1 from TTProdPrice T where T.LocID = D.LocationFrom and T.ProdZoneID = D.ZoneID and T.Article = S.ArtIngredient)'||
' and not exists (select 1 from TTProdPrice T where T.LocID = D.LocationFrom and T.ProdZoneID = -2 and T.Article = S.ArtIngredient)'||
')';
EXECUTE IMMEDIATE VSQL;
END;
BEGIN
DELETE FROM TTPRODPRICE;
LOADCALCULATION('0');
LOADPRODEXPENSES('0');
IF (ANEARESTDATE<>'0') THEN
LOADCALCULATION('1');
LOADPRODEXPENSES('1');
END IF;
END;
PROCEDURE LOADPRODPRICES(ADOCTYPE IN TDOCTYPE,
ADOCID IN TDOCID,
ALOCATIONID IN TLOCATION,
AZONEID IN TZONEID,
ADOCDATE IN TDATE,
ANEARESTDATE IN VARCHAR2,
AONLYFORZEROS IN VARCHAR2)
IS
BEGIN
REPTOOLS.CLEARCLASS;
REPTOOLS.CLEARLOC;
REPTOOLS.LOADLOC(1, TO_CHAR(ALOCATIONID), -2, -2);
REPTOOLS.LOADCLASSDOC(2, 1, NULL, ADOCTYPE, ADOCID);
LOADARTICLEPRODPRICES(ADOCDATE, ANEARESTDATE, AZONEID);
DELETE FROM TTLISPECITEMPRICE;
INSERT INTO TTLISPECITEMPRICE(SPECITEM, ARTICLE, ITEMPRICE)
SELECT S.SPECITEM, T.ARTICLE, T.PRICE
FROM TTPRODPRICE T, SMSPEC S
WHERE S.ARTICLE = T.ARTICLE
AND S.DOCTYPE = ADOCTYPE
AND S.DOCID = ADOCID
AND ((AONLYFORZEROS='0') OR NVL(S.ITEMPRICE,0)=0)
;
INSERT INTO TTLISPECITEMPRICE(SPECITEM, ARTICLE, ITEMPRICE)
SELECT S.SPECITEM, S.ARTICLE, 0
FROM SMSPEC S
WHERE S.DOCTYPE = ADOCTYPE
AND S.DOCID = ADOCID
AND ((AONLYFORZEROS='0') OR NVL(S.ITEMPRICE,0)=0)
AND NOT EXISTS (SELECT 1 FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM)
;
END;
PROCEDURE SETALLPRICESRL(ADOCID IN TDOCID,
AZEROFORNULLPRICETYPE IN VARCHAR2,
ANEARESTDATE IN VARCHAR2,
AONLYFORZEROS IN VARCHAR2
)
IS
VLOCATION TLOCATION;
VZONEID TZONEID;
VCREATEDAT TDATE;
VPRICEMODE SMROLLS.PRICEMODE%TYPE;
VPRICETYPE SMROLLS.PRICETYPE%TYPE;
VWITHDUE SMROLLS.WITHDUE%TYPE;
VISROUBLES SMDOCUMENTS.ISROUBLES%TYPE;
VCURRENCYRATE SMCURRRATES.SHOPRATE%TYPE;
VCURRENCYMULTORDER SMCURRRATES.MULTORDER%TYPE;
VCURRENCYPRECORDER SMCURRENCIES.PRECORDER%TYPE;
VCURRENCYMAINPRECORDER SMCURRENCIES.PRECORDER%TYPE;
CURSOR VSMSPEC IS
SELECT ARTICLE,SPECITEM,ITEMPRICE FROM SMSPEC
WHERE DOCID=ADOCID AND DOCTYPE='RL'
FOR UPDATE;
VPRICE NUMBER;
VPRICE2 NUMBER;
BEGIN
CORE.CHECKUPDATE('RL', ADOCID);
SELECT RL.LOCATION, RL.ZONEID, RL.CREATEDAT, RL.WITHDUE, RL.PRICEMODE, RL.PRICETYPE, RL.ISROUBLES,
RL.CURRENCYRATE, RL.CURRENCYMULTORDER, C.PRECORDER
INTO VLOCATION, VZONEID, VCREATEDAT, VWITHDUE, VPRICEMODE, VPRICETYPE, VISROUBLES,
VCURRENCYRATE, VCURRENCYMULTORDER, VCURRENCYPRECORDER
FROM SVDOCUMENTSRL RL, SMCURRENCIES C
WHERE RL.ID=ADOCID AND C.ID=RL.CURRENCYTYPE;
SELECT PRECORDER INTO VCURRENCYMAINPRECORDER
FROM SMCURRENCIES
WHERE ID = 0;
IF VPRICEMODE = 2
THEN
LOADPRODPRICES('RL', ADOCID, VLOCATION, VZONEID, VCREATEDAT, ANEARESTDATE, AONLYFORZEROS);
UPDATE SMSPEC S
SET ( ITEMPRICE,
ITEMPRICENOTAX,
ITEMPRICECUR,
TOTALPRICE,
TOTALPRICENOTAX,
TOTALPRICECUR
)
= (SELECT T.ITEMPRICE,
T.ITEMPRICE,
ROUND(T.ITEMPRICE/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER), -VCURRENCYPRECORDER),
ROUND(T.ITEMPRICE * S.QUANTITY, -VCURRENCYMAINPRECORDER),
ROUND(T.ITEMPRICE * S.QUANTITY, -VCURRENCYMAINPRECORDER),
ROUND(ROUND(T.ITEMPRICE/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER), -VCURRENCYPRECORDER)
* S.QUANTITY, -VCURRENCYPRECORDER)
FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM
)
WHERE S.DOCID = ADOCID
AND S.DOCTYPE = 'RL'
AND EXISTS (SELECT 1 FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM);
UPDATE SMSPECRL S
SET ( AWAITTOTALPRICE,
AWAITTOTALPRICECUR
)
= (SELECT ROUND(T.ITEMPRICE * S.AWAITQUANTITY, -VCURRENCYMAINPRECORDER),
ROUND(ROUND(T.ITEMPRICE/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER), -VCURRENCYPRECORDER)
* S.AWAITQUANTITY, -VCURRENCYPRECORDER)
FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM
)
WHERE S.DOCID = ADOCID
AND S.DOCTYPE = 'RL'
AND EXISTS (SELECT 1 FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM);
ELSIF VPRICEMODE = 3
THEN
LASTINC.LOADLASTINCOMEPRICES('RL', ADOCID, VLOCATION, VCREATEDAT,
ADMINCORE.GETSYSPARAMVALUE('LastIncomeModeRL', '2'),
AONLYFORZEROS, ANEARESTDATE, '1');
UPDATE SMSPEC S
SET ( ITEMPRICE,
ITEMPRICENOTAX,
ITEMPRICECUR,
TOTALPRICE,
TOTALPRICENOTAX,
TOTALPRICECUR
)
= (SELECT DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX),
DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX),
ROUND(DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX)/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER), -VCURRENCYPRECORDER),
ROUND(DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX) * S.QUANTITY, -VCURRENCYMAINPRECORDER),
ROUND(DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX) * S.QUANTITY, -VCURRENCYMAINPRECORDER),
ROUND(ROUND(DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX)/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER), -VCURRENCYPRECORDER)
* S.QUANTITY, -VCURRENCYPRECORDER)
FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM
)
WHERE S.DOCID = ADOCID AND S.DOCTYPE = 'RL'
AND EXISTS (SELECT 1 FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM)
;
UPDATE SMSPECRL S
SET ( AWAITTOTALPRICE,
AWAITTOTALPRICECUR
)
= (SELECT ROUND(DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX) * S.AWAITQUANTITY, -VCURRENCYMAINPRECORDER),
ROUND(ROUND(DECODE(VWITHDUE,'1',T.ITEMPRICE,T.ITEMPRICENOTAX)/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER), -VCURRENCYPRECORDER)
* S.AWAITQUANTITY, -VCURRENCYPRECORDER)
FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM
)
WHERE S.DOCID = ADOCID AND S.DOCTYPE = 'RL'
AND EXISTS (SELECT 1 FROM TTLISPECITEMPRICE T
WHERE T.SPECITEM = S.SPECITEM)
;
ELSIF VPRICEMODE = 0
THEN
ASSERT(VPRICETYPE>=0);
FOR VREC IN VSMSPEC
LOOP
IF (AONLYFORZEROS='0') OR NVL(VREC.ITEMPRICE,0)=0
THEN
IF (ANEARESTDATE<>'0') THEN
VPRICE := NVL(DOCUMENTS.GETARTICLEPRICEONNEARESTDATE(VREC.ARTICLE,VPRICETYPE,VLOCATION,VCREATEDAT), 0);
ELSE
VPRICE := NVL(DOCUMENTS.GETARTICLEPRICEONDATE(VREC.ARTICLE,VPRICETYPE,VLOCATION,VCREATEDAT),0);
END IF;
IF VISROUBLES = '1' THEN
VPRICE := ROUND(VPRICE,-VCURRENCYMAINPRECORDER);
VPRICE2 := ROUND(VPRICE/VCURRENCYRATE*POWER(10, VCURRENCYMULTORDER),-VCURRENCYPRECORDER);
ELSE
VPRICE2 := ROUND(VPRICE,-VCURRENCYPRECORDER);
VPRICE := ROUND(VPRICE2*VCURRENCYRATE/POWER(10, VCURRENCYMULTORDER), -VCURRENCYMAINPRECORDER);
END IF;
UPDATE SMSPEC
SET
ITEMPRICE = VPRICE,
ITEMPRICENOTAX = VPRICE,
ITEMPRICECUR = VPRICE2,
TOTALPRICE = ROUND(VPRICE * QUANTITY, -VCURRENCYMAINPRECORDER),
TOTALPRICENOTAX = ROUND(VPRICE * QUANTITY, -VCURRENCYMAINPRECORDER),
TOTALPRICECUR = ROUND(VPRICE2 * QUANTITY, -VCURRENCYPRECORDER)
WHERE CURRENT OF VSMSPEC;
UPDATE SMSPECRL
SET
AWAITTOTALPRICE = ROUND(VPRICE * AWAITQUANTITY, -VCURRENCYMAINPRECORDER),
AWAITTOTALPRICECUR = ROUND(VPRICE2 * AWAITQUANTITY, -VCURRENCYPRECORDER)
WHERE DOCID=ADOCID
AND DOCTYPE='RL'
AND SPECITEM=VREC.SPECITEM;
END IF;
END LOOP;
ELSIF VPRICEMODE = 1
THEN
ASSERT((AZEROFORNULLPRICETYPE<>'0'));
UPDATE SMSPEC
SET ITEMPRICE = NULL,
ITEMPRICECUR = NULL,
ITEMPRICENOTAX = NULL,
TOTALPRICE = 0,
TOTALPRICECUR = 0,
TOTALPRICENOTAX = 0
WHERE DOCTYPE = 'RL' AND DOCID = ADOCID;
UPDATE SMSPECRL
SET AWAITTOTALPRICE = 0,
AWAITTOTALPRICECUR = 0
WHERE DOCID = ADOCID AND DOCTYPE = 'RL';
DELETE FROM SMSPECRLBASES WHERE DOCID=ADOCID AND DOCTYPE='RL';
END IF;
END;
FUNCTION GETPURCHASINGSUM(AID IN SMDOCUMENTS.ID%TYPE,
ASPECITEM IN SMSPEC.SPECITEM%TYPE,
AQUANTITY IN SMSPEC.QUANTITY%TYPE
) RETURN SMSPEC.TOTALPRICE%TYPE
IS
PURE_PRICE NUMBER;
PORTION SMSPEC.QUANTITY%TYPE;
RESULT SMSPEC.TOTALPRICE%TYPE;
VCURRENCYPRECORDER INT;
QUANTITY SMSPEC.QUANTITY%TYPE;
VWITHDUE SMPRICETYPES.WITHDUE%TYPE;
FUNCTION GETPUREPRICE(AINCOMETYPE IN SMDOCUMENTS.DOCTYPE%TYPE,
AINCOMEID IN SMDOCUMENTS.ID%TYPE,
AINCOMEITEM IN SMSPEC.SPECITEM%TYPE
) RETURN NUMBER
IS
VINCOMEPRICE NUMBER;
BEGIN
SELECT NVL(DECODE(VWITHDUE, '1', ITEMPRICE, ITEMPRICENOTAX),0) INTO VINCOMEPRICE
FROM SMSPEC
WHERE DOCTYPE = AINCOMETYPE
AND DOCID = AINCOMEID
AND SPECITEM = AINCOMEITEM;
RETURN VINCOMEPRICE;
END GETPUREPRICE;
FUNCTION GETLASTPUREPRICE RETURN NUMBER
IS
BEGIN
FOR RBASE IN (SELECT BASEDOCTYPE, BASEDOCID, BASESPECITEM
FROM SMSPECRLBASES
WHERE DOCTYPE = 'RL'
AND DOCID = AID
AND SPECITEM= ASPECITEM
AND BASEDOCTYPE IS NOT NULL
ORDER BY ORDNUM DESC)
LOOP
RETURN GETPUREPRICE(RBASE.BASEDOCTYPE, RBASE.BASEDOCID, RBASE.BASESPECITEM);
END LOOP;
RETURN 0;
END GETLASTPUREPRICE;
BEGIN
RESULT:=0;
QUANTITY:=AQUANTITY;
BEGIN
SELECT RL.WITHDUE INTO VWITHDUE
FROM SVDOCUMENTSRL RL
WHERE RL.ID = AID;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54),'RL'||'.'||AID);
END;
SELECT PRECORDER INTO VCURRENCYPRECORDER
FROM SMCURRENCIES
WHERE ID = 0;
IF QUANTITY > 0
THEN
FOR RBASE IN (SELECT BASEDOCTYPE,BASEDOCID,BASESPECITEM,QUANTITY
FROM SMSPECRLBASES
WHERE DOCTYPE = 'RL'
AND DOCID = AID
AND SPECITEM = ASPECITEM
AND QUANTITY > 0
AND BASEDOCTYPE IS NOT NULL
ORDER BY ORDNUM DESC)
LOOP
PURE_PRICE := GETPUREPRICE(RBASE.BASEDOCTYPE, RBASE.BASEDOCID, RBASE.BASESPECITEM);
IF QUANTITY < RBASE.QUANTITY THEN
PORTION := QUANTITY;
ELSE
PORTION := RBASE.QUANTITY;
END IF;
RESULT := RESULT + ROUND(PURE_PRICE * PORTION, -VCURRENCYPRECORDER);
QUANTITY := QUANTITY - PORTION;
END LOOP;
IF QUANTITY > 0 THEN
PURE_PRICE := GETLASTPUREPRICE;
RESULT := RESULT + ROUND(PURE_PRICE * QUANTITY, -VCURRENCYPRECORDER);
END IF;
ELSIF QUANTITY < 0
THEN
QUANTITY := ABS(QUANTITY);
PURE_PRICE := GETLASTPUREPRICE;
RESULT := ROUND(PURE_PRICE * QUANTITY, -VCURRENCYPRECORDER);
END IF;
IF AQUANTITY < 0 THEN
RESULT := -RESULT;
END IF;
RETURN RESULT;
END GETPURCHASINGSUM;
PROCEDURE SETPURCHASINGPRICE(AID IN TDOCID)
IS
VTOTALPRICE SMSPEC.TOTALPRICE%TYPE;
VCURRENCYRATE SMCURRRATES.SHOPRATE%TYPE;
VCURRENCYMULTORDER SMCURRRATES.MULTORDER%TYPE;
VCURRENCYPRECORDER SMCURRENCIES.PRECORDER%TYPE;
VCURRENCYMAINPRECORDER SMCURRENCIES.PRECORDER%TYPE;
BEGIN
SELECT M.CURRENCYRATE, C.PRECORDER, M.CURRENCYMULTORDER
INTO VCURRENCYRATE, VCURRENCYPRECORDER, VCURRENCYMULTORDER
FROM SVDOCUMENTSRL M, SMCURRENCIES C
WHERE C.ID = M.CURRENCYTYPE
AND M.ID = AID;
SELECT PRECORDER INTO VCURRENCYMAINPRECORDER
FROM SMCURRENCIES WHERE ID = 0;
FOR VSPEC IN (SELECT SPECITEM, QUANTITY, AWAITQUANTITY
FROM SVSPECRL
WHERE DOCID=AID)
LOOP
VTOTALPRICE := GETPURCHASINGSUM(AID, VSPEC.SPECITEM, VSPEC.QUANTITY);
UPDATE SMSPEC
SET
TOTALPRICE = VTOTALPRICE,
TOTALPRICECUR = ROUND(VTOTALPRICE/VCURRENCYRATE*POWER(10,VCURRENCYMULTORDER),-VCURRENCYPRECORDER),
ITEMPRICE = NULL,
ITEMPRICECUR = NULL
WHERE DOCTYPE='RL' AND DOCID=AID AND SPECITEM=VSPEC.SPECITEM;
VTOTALPRICE := GETPURCHASINGSUM(AID, VSPEC.SPECITEM, VSPEC.AWAITQUANTITY);
UPDATE SMSPECRL
SET
AWAITTOTALPRICE = VTOTALPRICE,
AWAITTOTALPRICECUR = ROUND(VTOTALPRICE/VCURRENCYRATE*POWER(10,VCURRENCYMULTORDER),-VCURRENCYPRECORDER)
WHERE DOCTYPE='RL' AND DOCID=AID AND SPECITEM=VSPEC.SPECITEM;
END LOOP;
END;
PROCEDURE UPDATEAWAITSUMMS(
AID IN SMSPEC.DOCID%TYPE,
ASPECITEM IN SMSPEC.SPECITEM%TYPE,
AAWAITTOTALPRICE IN SMSPECRL.AWAITTOTALPRICE%TYPE,
AAWAITTOTALPRICECUR IN SMSPECRL.AWAITTOTALPRICECUR%TYPE
)
IS
BEGIN
UPDATE SMSPECRL
SET
AWAITTOTALPRICE = AAWAITTOTALPRICE,
AWAITTOTALPRICECUR = AAWAITTOTALPRICECUR
WHERE DOCTYPE = 'RL'
AND DOCID = AID
AND SPECITEM = ASPECITEM;
IF SQL%NOTFOUND THEN
INSERT INTO SMSPECRL
(DOCTYPE,DOCID,SPECITEM,AWAITQUANTITY,AWAITTOTALPRICE,AWAITTOTALPRICECUR)
VALUES
('RL',AID,ASPECITEM,0,AAWAITTOTALPRICE,AAWAITTOTALPRICECUR);
END IF;
END;
PROCEDURE ADDSPEC(
AID IN SMSPEC.DOCID%TYPE,
AARTICLE IN SMSPEC.ARTICLE%TYPE,
ADISPLAYITEM IN SMSPEC.DISPLAYITEM%TYPE,
AQUANTITY IN SMSPEC.QUANTITY%TYPE,
AITEMPRICE IN SMSPEC.ITEMPRICE%TYPE,
AITEMPRICECUR IN SMSPEC.ITEMPRICECUR%TYPE,
ATOTALPRICE IN SMSPEC.TOTALPRICE%TYPE,
ATOTALPRICECUR IN SMSPEC.TOTALPRICECUR%TYPE
)
IS
BEGIN
DOC3.ADDSPECP('RL', AID, AARTICLE, ADISPLAYITEM, AQUANTITY,
AITEMPRICE, AITEMPRICECUR, ATOTALPRICE, ATOTALPRICECUR,
NULL,NULL,NULL);
INSERT INTO SMSPECRL (DOCTYPE,DOCID,SPECITEM,
AWAITQUANTITY,AWAITTOTALPRICE,
AWAITTOTALPRICECUR)
SELECT 'RL',AID,MAX(SPECITEM),0,0,0
FROM SMSPEC SP
WHERE SP.DOCID=AID AND SP.DOCTYPE='RL';
DOCUMENTS.DROPDOCPROP('RL', AID, 'DocRL.Rest');
END;
PROCEDURE UPDHEAD(
AID IN SMDOCUMENTS.ID%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
APREAMBLEDATE IN SMROLLS.PREAMBLEDATE%TYPE,
AFINALDATE IN SMROLLS.FINALDATE%TYPE,
AWITHDUE IN SMROLLS.WITHDUE%TYPE,
ACURRENCYTYPE IN SMDOCUMENTS.CURRENCYTYPE%TYPE,
ACURRENCYRATE IN SMDOCUMENTS.CURRENCYRATE%TYPE,
ACURRENCYMULTORDER IN SMDOCUMENTS.CURRENCYMULTORDER%TYPE,
ACOMMENTARY IN SMDOCUMENTS.COMMENTARY%TYPE
)
IS
VCREATEDAT SMDOCUMENTS.CREATEDAT%TYPE;
VCURRENCYTYPE SMDOCUMENTS.CURRENCYTYPE%TYPE;
VISROUBLES SMDOCUMENTS.ISROUBLES%TYPE;
BEGIN
IF ACURRENCYTYPE IS NOT NULL THEN
SELECT ISROUBLES, CURRENCYTYPE, CREATEDAT
INTO VISROUBLES, VCURRENCYTYPE, VCREATEDAT
FROM SMDOCUMENTS
WHERE ID=AID AND DOCTYPE='RL';
ASSERT(VISROUBLES='1' OR VCURRENCYTYPE=ACURRENCYTYPE);
IF ACREATEDAT IS NOT NULL THEN
VCREATEDAT := ACREATEDAT;
END IF;
UPDATE SMDOCUMENTS
SET
CREATEDAT = VCREATEDAT,
CURRENCYTYPE = ACURRENCYTYPE,
CURRENCYRATE = ACURRENCYRATE,
CURRENCYMULTORDER = ACURRENCYMULTORDER,
COMMENTARY = ACOMMENTARY
WHERE ID=AID AND DOCTYPE='RL';
ELSIF ACREATEDAT IS NOT NULL THEN
UPDATE SMDOCUMENTS
SET
CREATEDAT = ACREATEDAT,
COMMENTARY = ACOMMENTARY
WHERE ID=AID AND DOCTYPE='RL';
ELSE
UPDATE SMDOCUMENTS
SET
COMMENTARY = ACOMMENTARY
WHERE ID=AID AND DOCTYPE='RL';
END IF;
IF ACREATEDAT IS NOT NULL THEN
UPDATE SMROLLS
SET
PREAMBLEDATE = APREAMBLEDATE,
FINALDATE = AFINALDATE,
WITHDUE = AWITHDUE
WHERE ID=AID AND DOCTYPE='RL';
ELSIF AWITHDUE IS NOT NULL THEN
UPDATE SMROLLS
SET
WITHDUE = AWITHDUE
WHERE ID=AID AND DOCTYPE='RL';
END IF;
END;
PROCEDURE UPDHEADDRAFT(
AID IN SMDOCUMENTS.ID%TYPE,
ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE,
APREAMBLEDATE IN SMROLLS.PREAMBLEDATE%TYPE,
AFINALDATE IN SMROLLS.FINALDATE%TYPE,
AWITHDUE IN SMROLLS.WITHDUE%TYPE,
ACURRENCYTYPE IN SMDOCUMENTS.CURRENCYTYPE%TYPE,
ACURRENCYRATE IN SMDOCUMENTS.CURRENCYRATE%TYPE,
ACURRENCYMULTORDER IN SMDOCUMENTS.CURRENCYMULTORDER%TYPE,
ACOMMENTARY IN SMDOCUMENTS.COMMENTARY%TYPE,
ASTAFFCHANGED IN VARCHAR2,
ACHAIRMANNAME IN TSTAFFNAME, ACHAIRMANPOSITION IN TSTAFFPOSITION,
ACOMMISSIONNAME1 IN TSTAFFNAME, ACOMMISSIONPOSITION1 IN TSTAFFPOSITION,
ACOMMISSIONNAME2 IN TSTAFFNAME, ACOMMISSIONPOSITION2 IN TSTAFFPOSITION,
ACOMMISSIONNAME3 IN TSTAFFNAME, ACOMMISSIONPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME1 IN TSTAFFNAME,ARESPONSIBLEPOSITION1 IN TSTAFFPOSITION,
ARESPONSIBLENAME2 IN TSTAFFNAME,ARESPONSIBLEPOSITION2 IN TSTAFFPOSITION,
ARESPONSIBLENAME3 IN TSTAFFNAME,ARESPONSIBLEPOSITION3 IN TSTAFFPOSITION,
ARESPONSIBLENAME4 IN TSTAFFNAME,ARESPONSIBLEPOSITION4 IN TSTAFFPOSITION
)
IS
BEGIN
UPDHEAD(AID, ACREATEDAT, APREAMBLEDATE, AFINALDATE, AWITHDUE,
ACURRENCYTYPE, ACURRENCYRATE, ACURRENCYMULTORDER,
ACOMMENTARY);
IF (ASTAFFCHANGED<>'0') THEN
SMSETDOCSTAFF('RL', AID, 0, ACHAIRMANNAME, ACHAIRMANPOSITION);
SMSETDOCSTAFF('RL', AID, 1, ACOMMISSIONNAME1, ACOMMISSIONPOSITION1);
SMSETDOCSTAFF('RL', AID, 2, ACOMMISSIONNAME2, ACOMMISSIONPOSITION2);
SMSETDOCSTAFF('RL', AID, 3, ACOMMISSIONNAME3, ACOMMISSIONPOSITION3);
SMSETDOCSTAFF('RL', AID, 4, ARESPONSIBLENAME1, ARESPONSIBLEPOSITION1);
SMSETDOCSTAFF('RL', AID, 5, ARESPONSIBLENAME2, ARESPONSIBLEPOSITION2);
SMSETDOCSTAFF('RL', AID, 6, ARESPONSIBLENAME3, ARESPONSIBLEPOSITION3);
SMSETDOCSTAFF('RL', AID, 7, ARESPONSIBLENAME4, ARESPONSIBLEPOSITION4);
END IF;
END UPDHEADDRAFT;
PROCEDURE SETPRICETYPE(
AID IN SMDOCUMENTS.ID%TYPE,
APRICEMODE IN SMROLLS.PRICEMODE%TYPE,
APRICETYPE IN TPRICETYPE
)
IS
VCURRENCY PRICES.TCURRRATEREC;
VISROUBLES SMDOCUMENTS.ISROUBLES%TYPE;
VLOCATION TLOCATION;
VCREATEDAT TDATE;
VWITHDUE SMPRICETYPES.WITHDUE%TYPE:='1';
BEGIN
SELECT LOCATION, CREATEDAT INTO VLOCATION, VCREATEDAT
FROM SMDOCUMENTS
WHERE DOCTYPE = 'RL' AND ID = AID;
VWITHDUE := GETWITHDUE(AID, APRICEMODE, APRICETYPE, VLOCATION);
IF APRICETYPE IS NULL
THEN
VISROUBLES := '1';
VCURRENCY := PRICES.GETCURRRATE(0, VCREATEDAT);
ELSE
BEGIN
SELECT CURRTYPE INTO VCURRENCY.IDCURRENCY
FROM SMPRICETYPES
WHERE ID = APRICETYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
VCURRENCY.IDCURRENCY := 0;
END;
VCURRENCY := PRICES.GETCURRRATE(VCURRENCY.IDCURRENCY, VCREATEDAT);
IF VCURRENCY.IDCURRENCY = 0
THEN
VISROUBLES := '1';
ELSE
VISROUBLES := '0';
END IF;
END IF;
IF VISROUBLES = '1' THEN
UPDATE SMDOCUMENTS
SET
ISROUBLES=VISROUBLES
WHERE ID=AID AND DOCTYPE='RL';
ELSE
UPDATE SMDOCUMENTS
SET CURRENCYTYPE = VCURRENCY.IDCURRENCY,
CURRENCYRATE = VCURRENCY.SHOPRATE,
CURRENCYMULTORDER = VCURRENCY.MULTORDER,
ISROUBLES = VISROUBLES
WHERE ID = AID
AND DOCTYPE = 'RL';
END IF;
UPDATE SMROLLS
SET PRICEMODE = APRICEMODE,
PRICETYPE = APRICETYPE,
WITHDUE = VWITHDUE
WHERE ID=AID AND DOCTYPE='RL';
UPDATE SMSPEC
SET TOTALPRICE = 0,
ITEMPRICE = NULL,
TOTALPRICECUR = 0,
ITEMPRICECUR = NULL
WHERE DOCID=AID AND DOCTYPE='RL';
UPDATE SMSPECRL
SET AWAITTOTALPRICE = 0,
AWAITTOTALPRICECUR = 0
WHERE DOCID=AID AND DOCTYPE='RL';
DELETE FROM SMSPECRLBASES
WHERE DOCID=AID AND DOCTYPE='RL';
END;
PROCEDURE GENERATEPRODSURPLUSWAYSRL(ADOCTYPE IN SMDOCUMENTS.DOCTYPE%TYPE
,ADOCID IN SMDOCUMENTS.ID%TYPE
,ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE
,ADOCID_WI IN SMDOCUMENTS.ID%TYPE
,ADOCID_PE IN SMDOCUMENTS.ID%TYPE
,ABLOCKFUTUREACTS IN INT
,AFAILSNUM OUT INT
,ACREATEDIN OUT INT)
IS
VCREATEDAT TDATE;
VOURSELFCLIENT TCLIENTID;
VCURRENCYTYPE SMDOCUMENTS.CURRENCYTYPE%TYPE;
VLOCATION TLOCATION;
VZONEID TZONEID;
VCOUNT INT;
VDATEBEFORE SMDOCUMENTS.CREATEDAT%TYPE;
PROCEDURE GENERATEPRODSURPLUSPE
IS
BEGIN
DOCPO.CREATEDOCUMENTPE(ADOCID_PE, VCREATEDAT, VLOCATION, VZONEID);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES('PE', ADOCID_PE, ADOCTYPE, ADOCID);
INSERT INTO SMSPEC(DOCTYPE,DOCID,SPECITEM,DISPLAYITEM,
ARTICLE,QUANTITY,
ITEMPRICENOTAX,TOTALPRICE,TOTALPRICECUR,TOTALPRICENOTAX)
SELECT 'PE',ADOCID_PE,SPECITEM,DISPLAYITEM,
ARTICLE,QUANTITYDIFFERENCE,
NVL(ITEMPRICE,0),0,0,0
FROM SVSPECRL RL
WHERE RL.DOCTYPE = ADOCTYPE AND RL.DOCID = ADOCID
AND QUANTITYDIFFERENCE>0;
INSERT INTO SMPRODEXPSPEC(DOCTYPE,DOCID,SPECITEM,ARTINGREDIENT,ALTQUANTITY)
SELECT 'PE',ADOCID_PE,SPECITEM,ARTICLE,QUANTITY
FROM SMSPEC S
WHERE S.DOCTYPE = 'PE' AND S.DOCID = ADOCID_PE;
DOCUMENTS.CHANGEDOCSTATE('PE', ADOCID_PE, 1, 2);
PRICECALCER.CALCDOC('PE', ADOCID_PE, NULL);
DOCUMENTS.UNLOCKDOC('PE', ADOCID_PE);
END;
PROCEDURE GENERATEPRODSURPLUSWI
IS
BEGIN
DOCUMENTS.CREATEDOCUMENTPROLOGUE(
'WI', ADOCID_WI, VCREATEDAT, ACLIENTINDEX, NULL, NULL, VLOCATION);
UPDATE SMDOCUMENTS SET
OPCODE=9,
USEROP=NULL,
CURRENCYTYPE=VCURRENCYTYPE,
PRICEROUNDMODE = 1
WHERE DOCTYPE='WI' AND ID=ADOCID_WI;
INSERT INTO SMWAYBILLSIN(ID, DOCTYPE, GOODSOWNER, PAYCASH, OURSELFCLIENT)
VALUES(ADOCID_WI, 'WI', 0, '0', VOURSELFCLIENT);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES('WI', ADOCID_WI, ADOCTYPE, ADOCID);
INSERT INTO SMSPEC(DOCTYPE,DOCID,SPECITEM,DISPLAYITEM,
ARTICLE,QUANTITY,
ITEMPRICE,ITEMPRICECUR,
TOTALPRICE,TOTALPRICECUR,TOTALPRICENOTAX)
SELECT 'WI',ADOCID_WI,SPECITEM,DISPLAYITEM,
ARTICLE,QUANTITYDIFFERENCE,
NVL(ITEMPRICE,0),NVL(ITEMPRICECUR,0),0,0,0
FROM SVSPECRL RL
WHERE RL.DOCTYPE = ADOCTYPE AND RL.DOCID = ADOCID
AND QUANTITYDIFFERENCE>0;
DOCUMENTS.CHANGEDOCSTATE('WI', ADOCID_WI, 1, 2);
PRICECALCER.CALCDOC('WI', ADOCID_WI, NULL);
DOCUMENTS.UNLOCKDOC('WI', ADOCID_WI);
END;
BEGIN
IF ADOCTYPE <> 'RL'
THEN
CORE.FAIL((-20000-55));
END IF;
SELECT NVL(COUNT(*),0) INTO VCOUNT
FROM SVSPECRL RL
WHERE RL.DOCTYPE = ADOCTYPE AND RL.DOCID = ADOCID
AND RL.QUANTITYDIFFERENCE>0;
IF VCOUNT = 0 THEN
ACREATEDIN := 0;
ELSE
BEGIN
SELECT D.CREATEDAT, D.LOCATION, D.ZONEID, D.OURSELFCLIENT, D.CURRENCYTYPE
INTO VCREATEDAT, VLOCATION, VZONEID, VOURSELFCLIENT, VCURRENCYTYPE
FROM SVDOCUMENTSRL D
WHERE D.DOCTYPE=ADOCTYPE AND D.ID=ADOCID;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54),ADOCTYPE||'.'||ADOCID);
END;
GENERATEPRODSURPLUSWI;
GENERATEPRODSURPLUSPE;
AFAILSNUM:=0;
IF ABLOCKFUTUREACTS = 1
THEN
VDATEBEFORE := TO_DATE('99991231','YYYYMMDD');
ELSE
VDATEBEFORE := VCREATEDAT;
END IF;
SMBLOCKPRODACTSBYROLLS(ADOCID, VDATEBEFORE, VLOCATION, VZONEID, AFAILSNUM);
ACREATEDIN := 1;
END IF;
END;
PROCEDURE GENERATEPRODDEFICITWAYSRL(ADOCTYPE IN SMDOCUMENTS.DOCTYPE%TYPE
,ADOCID IN SMDOCUMENTS.ID%TYPE
,ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE
,ADOCID_PO IN SMDOCUMENTS.ID%TYPE
,ADOCID_WO IN SMDOCUMENTS.ID%TYPE
,ABLOCKFUTUREACTS IN INT
,AFAILSNUM OUT INT
,ACREATEDIN OUT INT
)
IS
VCREATEDAT TDATE;
VOURSELFCLIENT TCLIENTID;
VCURRENCYTYPE SMDOCUMENTS.CURRENCYTYPE%TYPE;
VLOCATION TLOCATION;
VZONEID TZONEID;
VCOUNT INT;
VDATEBEFORE SMDOCUMENTS.CREATEDAT%TYPE;
PROCEDURE GENERATEPRODDEFICITPO
IS
BEGIN
DOCPO.CREATEDOCUMENTPO(ADOCID_PO, VCREATEDAT, VLOCATION, VZONEID);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES('PO',ADOCID_PO,ADOCTYPE,ADOCID);
INSERT INTO SMSPEC(DOCTYPE,DOCID,SPECITEM,DISPLAYITEM,
ARTICLE,QUANTITY,
ITEMPRICENOTAX,TOTALPRICE,TOTALPRICECUR,TOTALPRICENOTAX)
SELECT 'PO',ADOCID_PO,SPECITEM,DISPLAYITEM,
ARTICLE,-QUANTITYDIFFERENCE,
NVL(ITEMPRICE,0),0,0,0
FROM SVSPECRL RL
WHERE DOCTYPE = ADOCTYPE AND DOCID = ADOCID
AND QUANTITYDIFFERENCE<0;
DOCUMENTS.CHANGEDOCSTATE('PO', ADOCID_PO, 1, 2);
PRICECALCER.CALCDOC('PO', ADOCID_PO, NULL);
DOCUMENTS.UNLOCKDOC('PO', ADOCID_PO);
END;
PROCEDURE GENERATEPRODDEFICITWO
IS
BEGIN
DOCUMENTS.CREATEDOCUMENTPROLOGUE(
'WO', ADOCID_WO, VCREATEDAT, ACLIENTINDEX, NULL, VLOCATION, NULL);
UPDATE SMDOCUMENTS SET
OPCODE=8,
USEROP=NULL,
CURRENCYTYPE=VCURRENCYTYPE,
PRICEROUNDMODE = 1
WHERE DOCTYPE='WO' AND ID=ADOCID_WO;
INSERT INTO SMWAYBILLSOUT(DOCTYPE, ID, PAYCASH, OURSELFCLIENT)
VALUES('WO', ADOCID_WO, '0', VOURSELFCLIENT);
INSERT INTO SMCOMMONBASES(DOCTYPE, ID, BASEDOCTYPE, BASEID)
VALUES('WO', ADOCID_WO, ADOCTYPE, ADOCID);
INSERT INTO SMSPEC(DOCTYPE,DOCID,SPECITEM,DISPLAYITEM,
ARTICLE,QUANTITY,
ITEMPRICE,ITEMPRICECUR,
TOTALPRICE,TOTALPRICECUR,TOTALPRICENOTAX)
SELECT 'WO',ADOCID_WO,SPECITEM,DISPLAYITEM,
ARTICLE,-QUANTITYDIFFERENCE,
NVL(ITEMPRICE,0),NVL(ITEMPRICECUR,0),0,0,0
FROM SVSPECRL RL
WHERE DOCTYPE = ADOCTYPE AND DOCID = ADOCID
AND QUANTITYDIFFERENCE<0;
DOCUMENTS.CHANGEDOCSTATE('WO', ADOCID_WO, 1, 2);
PRICECALCER.CALCDOC('WO', ADOCID_WO, NULL);
DOCUMENTS.UNLOCKDOC('WO', ADOCID_WO);
END;
BEGIN
IF ADOCTYPE <> 'RL'
THEN
CORE.FAIL((-20000-55));
END IF;
SELECT NVL(COUNT(*),0) INTO VCOUNT
FROM SVSPECRL RL
WHERE RL.DOCTYPE = ADOCTYPE AND RL.DOCID = ADOCID
AND RL.QUANTITYDIFFERENCE<0;
IF VCOUNT = 0 THEN
ACREATEDIN := 0;
ELSE
BEGIN
SELECT D.CREATEDAT, D.LOCATION, D.ZONEID, D.OURSELFCLIENT, D.CURRENCYTYPE
INTO VCREATEDAT, VLOCATION, VZONEID, VOURSELFCLIENT, VCURRENCYTYPE
FROM SVDOCUMENTSRL D
WHERE D.DOCTYPE=ADOCTYPE AND D.ID=ADOCID;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54),ADOCTYPE||'.'||ADOCID);
END;
GENERATEPRODDEFICITPO;
GENERATEPRODDEFICITWO;
AFAILSNUM:=0;
IF ABLOCKFUTUREACTS = 1
THEN
VDATEBEFORE := TO_DATE('99991231','YYYYMMDD');
ELSE
VDATEBEFORE := VCREATEDAT;
END IF;
SMBLOCKPRODACTSBYROLLS(ADOCID, VDATEBEFORE, VLOCATION, VZONEID, AFAILSNUM);
ACREATEDIN := 1;
END IF;
END;
PROCEDURE CLOSEDEDITDATEAFTERINVENTORY(ADOCID IN TDOCID)
IS
VDATE TDATE;
VLOC TLOCATION;
BEGIN
IF (ADMINCORE.GETSYSPARAMVALUE('NotEditDocAfterInventory', '0')<>'0')
THEN
BEGIN
SELECT D.PREAMBLEDATE, D.LOCATION INTO VDATE, VLOC
FROM SVDOCNOPRICERL D
WHERE D.ID = ADOCID;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54),'RL'||'.'||ADOCID);
END;
UPDATE SMSTORELOCATIONS L
SET L.CLOSEDEDITDATE = VDATE,
L.CLOSEDEDITDOCID = ADOCID,
L.CLOSEDEDITDATEOLD = L.CLOSEDEDITDATE,
L.CLOSEDEDITDOCIDOLD = L.CLOSEDEDITDOCID
WHERE L.ID = VLOC
AND (L.CLOSEDEDITDATE IS NULL OR L.CLOSEDEDITDATE < VDATE);
END IF;
END;
PROCEDURE CANCELEDITDATEAFTERINVENTORY(ADOCID IN TDOCID)
IS
VLOC TLOCATION;
BEGIN
IF (ADMINCORE.GETSYSPARAMVALUE('NotEditDocAfterInventory', '0')<>'0')
THEN
BEGIN
SELECT D.LOCATION INTO VLOC
FROM SVDOCNOPRICERL D
WHERE D.ID = ADOCID;
EXCEPTION WHEN NO_DATA_FOUND THEN
CORE.FAILEX((-20000-54),'RL'||'.'||ADOCID);
END;
UPDATE SMSTORELOCATIONS L
SET L.CLOSEDEDITDATE = L.CLOSEDEDITDATEOLD,
L.CLOSEDEDITDOCID = L.CLOSEDEDITDOCIDOLD,
L.CLOSEDEDITDATEOLD = NULL,
L.CLOSEDEDITDOCIDOLD = NULL
WHERE L.ID = VLOC
AND L.CLOSEDEDITDOCID = ADOCID;
END IF;
END;
PROCEDURE GENERATEDOCSRL(AISACTS IN INT
,ACLIENTINDEX IN SMDOCUMENTS.CLIENTINDEX%TYPE
,AISJOINEXISTACTS IN INT
,AISBLOCKFUTUREACTS IN INT)
IS
VFAILSNUM INT;
VCLIENTID TCLIENTID;
PROCEDURE WRITEERROR(ADOCID IN TDOCID, ADOCTYPE IN TDOCTYPE, ACOMMENT IN SMDOCUMENTS.COMMENTARY%TYPE)
IS
BEGIN
UPDATE TTPAYMENTSONWAYBILLSLISTIN T
SET T.SUCCESS = '0',
T.COMMENTARY = COMMENTARY || ACOMMENT
WHERE T.DOCTYPE = ADOCTYPE AND T.ID = ADOCID;
END;
PROCEDURE CREATEDOCACTS(ADOCRL IN TDOCID, ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE, ALOCID IN TLOCATION)
IS
VDOCFA TDOCID;
VDOCLA TDOCID;
BEGIN
VDOCFA:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'FA', ALOCID);
VDOCLA:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'LA', ALOCID);
SMGENERATEACTSRL(ADOCRL, ACREATEDAT, ALOCID, VDOCFA, VDOCLA, AISJOINEXISTACTS, AISBLOCKFUTUREACTS, VFAILSNUM);
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'FA', VDOCFA, '1' FROM DUAL WHERE VDOCFA IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'LA', VDOCLA, '1' FROM DUAL WHERE VDOCLA IS NOT NULL;
END;
PROCEDURE CREATEDOCWAYBILLS(ADOCRL IN TDOCID, ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE, ALOCID IN TLOCATION)
IS
VDOCWI TDOCID;
VDOCWO TDOCID;
VDOCSR TDOCID;
VDOCIWI TDOCID;
VDOCIWO TDOCID;
BEGIN
VDOCWI:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'WI', ALOCID);
VDOCWO:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'WO', ALOCID);
VDOCSR:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'SR', ALOCID);
VDOCIWI:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'WI', ALOCID);
VDOCIWO:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'WO', ALOCID);
SMGENERATEWAYSRL(ADOCRL, ACREATEDAT, ALOCID, VCLIENTID, VDOCWI, VDOCWO, VDOCSR,
VDOCIWI, VDOCIWO, AISBLOCKFUTUREACTS, VFAILSNUM);
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'WI', VDOCWI, '1' FROM DUAL WHERE VDOCWI IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'WO', VDOCWO, '1' FROM DUAL WHERE VDOCWO IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'SR', VDOCSR, '1' FROM DUAL WHERE VDOCSR IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'WI', VDOCIWI, '1' FROM DUAL WHERE VDOCIWI IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'WO', VDOCIWO, '1' FROM DUAL WHERE VDOCIWO IS NOT NULL;
END;
PROCEDURE CREATEPRODDOCACTS(ADOCRL IN TDOCID, ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE, ALOCID IN TLOCATION, AZONEID IN SMROLLS.ZONEID%TYPE)
IS
VDOCPF TDOCID;
VDOCPW TDOCID;
VISCREATEPF CHAR(1);
VISCREATEPW CHAR(1);
BEGIN
VDOCPF:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'PF', ALOCID);
VDOCPW:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'PW', ALOCID);
SMGENERATEPRODACTSRL(ADOCRL, ACREATEDAT, ALOCID, AZONEID, VDOCPF, VDOCPW, AISJOINEXISTACTS, AISBLOCKFUTUREACTS,
VFAILSNUM, VISCREATEPF, VISCREATEPW);
IF VDOCPF IS NOT NULL AND VISCREATEPF = '1'
THEN
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) VALUES('PF', VDOCPF, '1');
DOCUMENTS.UNLOCKDOC('PF', VDOCPF);
END IF;
IF VDOCPW IS NOT NULL AND VISCREATEPW = '1'
THEN
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) VALUES('PW', VDOCPW, '1');
DOCUMENTS.UNLOCKDOC('PW', VDOCPW);
END IF;
END;
PROCEDURE CREATEPRODDOCWAYBILLS(ADOCRL IN TDOCID, ACREATEDAT IN SMDOCUMENTS.CREATEDAT%TYPE, ALOCID IN TLOCATION)
IS
VDOCWI TDOCID;
VDOCPE TDOCID;
VDOCWO TDOCID;
VDOCPO TDOCID;
VISCREATEIN CHAR(1);
VISCREATEOUT CHAR(1);
BEGIN
VDOCWI:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'WI', ALOCID);
VDOCPE:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'PE', ALOCID);
VDOCPO:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'PO', ALOCID);
VDOCWO:= SMGENERATEDOCNOROLLSWAYS(ADOCRL, 'WO', ALOCID);
SMGENERATEPRODSURPLUSWAYSRL1('RL', ADOCRL, VCLIENTID, VDOCWI, VDOCPE, AISBLOCKFUTUREACTS, VFAILSNUM, VISCREATEIN);
SMGENERATEPRODDEFICITWAYSRL1('RL', ADOCRL, VCLIENTID, VDOCPO, VDOCWO, AISBLOCKFUTUREACTS, VFAILSNUM, VISCREATEOUT);
IF VISCREATEIN = '1'
THEN
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'WI', VDOCWI, '1' FROM DUAL WHERE VDOCWI IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'PE', VDOCPE, '1' FROM DUAL WHERE VDOCPE IS NOT NULL;
END IF;
IF VISCREATEOUT = '1'
THEN
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'PO', VDOCPO, '1' FROM DUAL WHERE VDOCPO IS NOT NULL;
INSERT INTO TTPAYMENTSONWAYBILLSLISTOUT(DOCTYPE, ID, SUCCESS) SELECT 'WO', VDOCWO, '1' FROM DUAL WHERE VDOCWO IS NOT NULL;
END IF;
END;
FUNCTION ISEXISTCHILDDOC(ADOCRL IN TDOCID, AZONEID IN TZONEID) RETURN BOOLEAN
IS
VCOUNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO VCOUNT FROM DUAL
WHERE EXISTS (SELECT 1 FROM SMCOMMONBASES B, SMDOCUMENTS D
WHERE D.DOCTYPE = B.DOCTYPE
AND D.ID = B.ID
AND B.BASEDOCTYPE = 'RL'
AND B.BASEID = ADOCRL
AND ((AZONEID IS NULL AND B.DOCTYPE IN ('WI', 'WO', 'SR'))
OR (AZONEID IS NOT NULL AND B.DOCTYPE IN ('WI', 'PE', 'PO', 'WO')))
AND D.DOCSTATE >= 2
);
RETURN VCOUNT > 0;
END;
BEGIN
SMINSPECTWARNINGOFF;
DOCGOODS.ENABLEACTUALIZEGOODS;
UPDATE TTPAYMENTSONWAYBILLSLISTIN T SET T.SUCCESS = '1', T.COMMENTARY = '';
DELETE FROM TTPAYMENTSONWAYBILLSLISTOUT;
FOR CUR IN (SELECT TT.ID, D.LOCATION, D.DOCSTATE, D.CREATEDAT, D.ZONEID
FROM TTPAYMENTSONWAYBILLSLISTIN TT, SVDOCUMENTSRL D
WHERE D.ID = TT.ID AND D.DOCTYPE = TT.DOCTYPE)
LOOP
IF CUR.DOCSTATE = 3
THEN
IF (AISACTS='0')
THEN
IF ISEXISTCHILDDOC(CUR.ID, CUR.ZONEID)
THEN
WRITEERROR(CUR.ID, 'RL', 'Документы на излишки и недостачу уже созданы.');
GOTO L_NEXTDOC;
END IF;
IF ACLIENTINDEX = -2
THEN
BEGIN
SELECT CLIENTID INTO VCLIENTID
FROM SMOWNCLIENTLOCS
WHERE LOCID = CUR.LOCATION;
EXCEPTION
WHEN NO_DATA_FOUND THEN
VCLIENTID := NULL;
WHEN TOO_MANY_ROWS THEN
VCLIENTID := NULL;
END;
ELSE
VCLIENTID := ACLIENTINDEX;
END IF;
IF VCLIENTID IS NULL OR VCLIENTID = -2 THEN
WRITEERROR(CUR.ID, 'RL', 'Не определен внешний контрагент для создаваемых документов.');
GOTO L_NEXTDOC;
END IF;
END IF;
BEGIN
DOCUMENTS.LOCKDOC('RL', CUR.ID);
EXCEPTION WHEN OTHERS
THEN
WRITEERROR(CUR.ID, 'RL', SQLERRM);
GOTO L_NEXTDOC;
END;
BEGIN
IF CUR.ZONEID IS NULL
THEN
IF (AISACTS<>'0') THEN
CREATEDOCACTS(CUR.ID, CUR.CREATEDAT, CUR.LOCATION);
ELSE
CREATEDOCWAYBILLS(CUR.ID, CUR.CREATEDAT, CUR.LOCATION);
END IF;
ELSE
IF (AISACTS<>'0') THEN
CREATEPRODDOCACTS(CUR.ID, CUR.CREATEDAT, CUR.LOCATION, CUR.ZONEID);
ELSE
CREATEPRODDOCWAYBILLS(CUR.ID, CUR.CREATEDAT, CUR.LOCATION);
END IF;
END IF;
EXCEPTION WHEN OTHERS
THEN
WRITEERROR(CUR.ID, 'RL', SQLERRM);
END;
DOCUMENTS.UNLOCKDOC('RL', CUR.ID);
ELSE
WRITEERROR(CUR.ID, 'RL', 'Статус отличен от "Принят в количестве и ценах".');
END IF;
<<L_NEXTDOC>> NULL;
END LOOP;
DOCGOODS.ACTUALIZEGOODS;
SMINSPECTWARNINGON;
END;
PROCEDURE CREATEDOCSBYOINV(APROCESSID IN TPROCESSID, AFAID OUT TDOCID, ALAID OUT TDOCID, AILID OUT TDOCID)
IS
VFAID TDOCID;
VLAID TDOCID;
VILID TDOCID;
VLOCID TLOCATION;
VCOUNT PLS_INTEGER;
VSTATELAFA INT := ADMINCORE.GETSYSPARAMNUM('TSDOINVCreateLAFA', -1);
VSTATEIL INT := ADMINCORE.GETSYSPARAMNUM('TSDOINVCreateIL', -1);
FUNCTION CREATEDOC (ADOCTYPE TDOCTYPE) RETURN TDOCID
IS
VDOCID TDOCID;
VPROCDATE SMPROCESS.STARTTIME%TYPE;
VCREATEDAT SMDOCUMENTS.CREATEDAT%TYPE;
VOURSELFCLIENT TCLIENTID;
VPRICETYPE TPRICETYPE;
VWITHDUE SMPRICETYPES.WITHDUE%TYPE := '1';
VCURRENCY PRICES.TCURRRATEREC;
VSPECTYPENAME SMINVLIST.SPECTYPENAME%TYPE;
BEGIN
VCREATEDAT :=TRUNC(SYSDATE);
VDOCID := DOCUMENTS.GENERATEDOCNO(ADOCTYPE, VLOCID);
DOCUMENTS.CREATEDOCUMENTPROLOGUE(ADOCTYPE, VDOCID, VCREATEDAT, NULL, VLOCID, NULL, NULL);
SELECT STARTTIME INTO VPROCDATE FROM SMPROCESS
WHERE PROCESSID = APROCESSID AND PROCESSTYPE = 'OINV';
IF VPROCDATE IS NOT NULL
THEN
VSPECTYPENAME := 'Контроль остатков ТСД № '||APROCESSID||' от '||TO_CHAR(VPROCDATE,'DD.MM.YYYY');
END IF;
IF ADOCTYPE = 'IL' THEN
BEGIN
SELECT CLIENTID INTO VOURSELFCLIENT FROM SMOWNCLIENTLOCS WHERE LOCID=VLOCID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
VOURSELFCLIENT := NULL;
WHEN TOO_MANY_ROWS THEN
VOURSELFCLIENT := NULL;
END;
SELECT LP.PRICETYPE, PT.WITHDUE, PT.CURRTYPE INTO VPRICETYPE, VWITHDUE, VCURRENCY.IDCURRENCY
FROM SMLOCPRICES LP, SMPRICETYPES PT
WHERE LP.LOCID = VLOCID
AND PT.ID = LP.PRICETYPE
AND BITAND(LP.FLAGS, 2) <> 0;
VCURRENCY := PRICES.GETCURRRATE(VCURRENCY.IDCURRENCY, VCREATEDAT);
IF VCURRENCY.IDCURRENCY <> 0 THEN
UPDATE SUPERMAG.SMDOCUMENTS
SET CURRENCYTYPE=VCURRENCY.IDCURRENCY,
CURRENCYRATE=VCURRENCY.SHOPRATE,
CURRENCYMULTORDER=VCURRENCY.MULTORDER,
ISROUBLES='0'
WHERE DOCTYPE='IL' AND ID=VDOCID;
END IF;
INSERT INTO SMINVLIST(ID, DOCTYPE, FILLSPECTYPE,
ISFILLCOMPLETE, ISACTIVEONLY, PRICEMODE, PRICETYPE,
ORDERNO, OURSELFCLIENT, PREAMBLEDATE, FINALDATE,
STORELOC, WITHDUE, SPECTYPENAME)
VALUES(VDOCID, 'IL', 0,
'0', '0', 0, VPRICETYPE,
VDOCID, VOURSELFCLIENT, VCREATEDAT, VCREATEDAT,
VLOCID, VWITHDUE, VSPECTYPENAME);
END IF;
DOCUMENTS.CREATEDOCUMENTEPILOGUE(ADOCTYPE, VDOCID);
SELECT STARTTIME INTO VPROCDATE FROM SMPROCESS
WHERE PROCESSID = APROCESSID AND PROCESSTYPE = 'OINV';
IF VPROCDATE IS NOT NULL
THEN
UPDATE SMDOCUMENTS
SET COMMENTARY = VSPECTYPENAME
WHERE DOCTYPE = ADOCTYPE AND ID = VDOCID;
END IF;
INSERT INTO SMPROCESSDOCCREATERESULT(PROCESSID, PROCESSTYPE, DOCTYPE, DOCID)
VALUES(APROCESSID, 'OINV', ADOCTYPE, VDOCID);
RETURN VDOCID;
END;
PROCEDURE FILLSPEC(ADOCID TDOCID, ADOCTYPE TDOCTYPE)
IS
VSPECITEM SMSPEC.SPECITEM%TYPE;
VDISPLAYITEM SMSPEC.DISPLAYITEM%TYPE;
BEGIN
DELETE FROM TTSPECSCALE;
FOR CUR IN (SELECT* FROM (SELECT S.SPECITEM, S.ARTICLE,S.SUBARTICLE, (SELECT NVL(SUM( L.QUANTITYBARCODE * L.QUANTITYSCAN * NVL(A.AMOUNT,1) ),0)
FROM SMPROCESSINVENTORYLOCOINV L, SMCOMPLEXARTICLES A
WHERE L.PROCESSTYPE = 'OINV'
AND L.PROCESSID = APROCESSID
AND A.COMPLEXARTICLE(+) = L.ARTICLE
AND NVL(A.ARTICLE,L.ARTICLE) = S.ARTICLE
AND (S.SUBARTICLE IS NULL OR S.SUBARTICLE = L.SUBARTICLE)
) QUANTITYBARCODE, S.REMAINSTART
FROM SMPROCESSINVENTORYSPECOINV S
WHERE S.PROCESSTYPE = 'OINV'
AND S.PROCESSID = APROCESSID)
WHERE (QUANTITYBARCODE - REMAINSTART > 0 AND ADOCTYPE = 'FA') OR (QUANTITYBARCODE - REMAINSTART < 0 AND ADOCTYPE = 'LA')
OR (ADOCTYPE = 'IL' AND QUANTITYBARCODE > 0))
LOOP
SELECT MAX(DISPLAYITEM)+1, MAX(SPECITEM)+1
INTO VDISPLAYITEM, VSPECITEM
FROM SMSPEC
WHERE DOCTYPE = ADOCTYPE
AND DOCID = ADOCID;
IF VDISPLAYITEM IS NULL THEN
VDISPLAYITEM := 1;
END IF;
IF VSPECITEM IS NULL
THEN
VSPECITEM := 1;
END IF;
IF CUR.SUBARTICLE IS NOT NULL
THEN
INSERT INTO TTSPECSCALE(SUBARTICLE, QUANTITY)
SELECT L.SUBARTICLE, NVL(SUM(NVL(L.QUANTITYSCAN,0) * NVL(L.QUANTITYBARCODE,0)),0)
FROM SMPROCESSINVENTORYLOCOINV L
WHERE L.PROCESSTYPE = 'OINV'
AND L.PROCESSID = APROCESSID
AND L.ARTICLE = CUR.ARTICLE
AND L.SUBARTICLE IS NOT NULL
GROUP BY L.SUBARTICLE;
END IF;
IF ADOCTYPE IN ('FA', 'LA') THEN
DOCUMENTS.ADDSPEC(ADOCTYPE, ADOCID, CUR.ARTICLE, ABS(CUR.QUANTITYBARCODE - CUR.REMAINSTART), VDISPLAYITEM, NULL, NULL, NULL);
ELSE
DOCUMENTS.ADDSPEC(ADOCTYPE, ADOCID, CUR.ARTICLE, CUR.QUANTITYBARCODE, VDISPLAYITEM, NULL, NULL, NULL);
END IF;
END LOOP;
END;
PROCEDURE TRYSETSTATE(ADOCID TDOCID, ADOCTYPE TDOCTYPE, ASTATE INT)
IS
VSTATENAME VARCHAR2(255);
BEGIN
IF ASTATE = 2 THEN
SELECT DOCSTATENAME INTO VSTATENAME
FROM SSDOCSTATES
WHERE DOCTYPE = ADOCTYPE
AND DOCSTATE = 2;
COMMIT;
BEGIN
DOCGOODS.ENABLEACTUALIZEGOODS;
SMINSPECTWARNINGOFF;
DOCUMENTS.CHANGEDOCSTATE(ADOCTYPE, ADOCID, 1, 2);
SMINSPECTWARNINGON;
DOCGOODS.ACTUALIZEGOODS;
DOCUMENTS.UNLOCKDOC (ADOCTYPE, ADOCID);
EXCEPTION WHEN OTHERS THEN
DOCUMENTS.UNLOCKDOC (ADOCTYPE, ADOCID);
RAISE;
END;
COMMIT;
ELSE
DOCUMENTS.UNLOCKDOC (ADOCTYPE, ADOCID);
END IF;
END;
BEGIN
IF VSTATELAFA = -1 AND VSTATEIL = -1 THEN
RETURN;
END IF;
SELECT LOCATION INTO VLOCID FROM SMPROCESSHEADEROINV
WHERE PROCESSTYPE = 'OINV'
AND PROCESSID = APROCESSID;
IF VSTATELAFA >= 0 THEN
SELECT COUNT(*) INTO VCOUNT FROM (SELECT S.REMAINSTART,
(SELECT NVL(SUM( L.QUANTITYBARCODE * L.QUANTITYSCAN * NVL(A.AMOUNT,1) ),0)
FROM SMPROCESSINVENTORYLOCOINV L, SMCOMPLEXARTICLES A
WHERE L.PROCESSTYPE = 'OINV'
AND L.PROCESSID = APROCESSID
AND A.COMPLEXARTICLE(+) = L.ARTICLE
AND NVL(A.ARTICLE,L.ARTICLE) = S.ARTICLE
AND (S.SUBARTICLE IS NULL OR S.SUBARTICLE = L.SUBARTICLE)
) QUANTITYBARCODE
FROM SMPROCESSINVENTORYSPECOINV S
WHERE S.PROCESSTYPE = 'OINV'
AND S.PROCESSID = APROCESSID)
WHERE QUANTITYBARCODE - REMAINSTART > 0;
IF VCOUNT > 0 THEN
VFAID := CREATEDOC('FA');
DOCUMENTS.LOCKDOC('FA', VFAID);
FILLSPEC(VFAID, 'FA');
TRYSETSTATE(VFAID, 'FA', VSTATELAFA);
END IF;
SELECT COUNT(*) INTO VCOUNT FROM (SELECT S.REMAINSTART,
(SELECT NVL(SUM( L.QUANTITYBARCODE * L.QUANTITYSCAN * NVL(A.AMOUNT,1) ),0)
FROM SMPROCESSINVENTORYLOCOINV L, SMCOMPLEXARTICLES A
WHERE L.PROCESSTYPE = 'OINV'
AND L.PROCESSID = APROCESSID
AND A.COMPLEXARTICLE(+) = L.ARTICLE
AND NVL(A.ARTICLE,L.ARTICLE) = S.ARTICLE
AND (S.SUBARTICLE IS NULL OR S.SUBARTICLE = L.SUBARTICLE)
) QUANTITYBARCODE
FROM SMPROCESSINVENTORYSPECOINV S
WHERE S.PROCESSTYPE = 'OINV'
AND S.PROCESSID = APROCESSID)
WHERE QUANTITYBARCODE - REMAINSTART < 0;
IF VCOUNT > 0 THEN
VLAID := CREATEDOC('LA');
DOCUMENTS.LOCKDOC('LA', VLAID);
FILLSPEC(VLAID, 'LA');
TRYSETSTATE(VLAID, 'LA', VSTATELAFA);
END IF;
END IF;
IF VSTATEIL >=0 THEN
VILID := CREATEDOC('IL');
DOCUMENTS.LOCKDOC('IL', VILID);
FILLSPEC(VILID, 'IL');
TRYSETSTATE(VILID, 'IL', VSTATEIL);
END IF;
UPDATE SMPROCESS SET COMPLETEDTIME=SYSDATE WHERE PROCESSTYPE='OINV' AND PROCESSID=APROCESSID;
AFAID := VFAID;
ALAID := VLAID;
AILID := VILID;
END;
BEGIN
NULL;
END DOCRL;
Часовой пояс GMT +3, время: 05:04.
Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.