Бизнес анализ. Движение артикула. Добавление контрагента. : Супермаг Плюс (Супермаг 2000)

Create or Replace Function Article_LastDocID_from_DATE(
                MyStoreloc IN SUPERMAG.SMSTORELOCATIONS.ID%TYPE, 
                MyArticle IN SUPERMAG.SMCARD.ARTICLE%TYPE, 
                MyOpCode IN SUPERMAG.SMDOCUMENTS.OPCODE%TYPE, 
                MyUserOp IN SUPERMAG.SMDOCUMENTS.USEROP%TYPE := null,
      MyDATE IN DATE
) 
return VARCHAR2 is
res_all varchar2 (100);
res_0 integer := -1;
BEGIN
        -- res_0 - признак типа МХ для операции: 0 - нет операции, 1 - расход, 2 - приход. -1 - внутреннее перемещение
        select (decode(a.incometype, 0, 
                    decode(a.expensetype, 0, 0, 1), 2))
            into res_0
       from supermag.saoperation a
            where a.id = MyOpCode
            and a.incometype*a.expensetype = 0;
        res_0:= nvl(res_0, -1);
        
        IF  res_0 >= 0 THEN 
        SELECT MAX(TO_CHAR(d.CREATEDAT, 'YYYYMMDD') || d.ID)
            INTO res_all
        FROM supermag.smdocuments d, supermag.smspec s
        WHERE d.opcode = MyOPCode
          and (d.userop = MyUserOp or MyUserOp is null)
          and d.createdat <= TRUNC(MyDATE)
          and decode(res_0, 0, d.location, 1, d.locationfrom, 2, d.locationto) = MyStoreloc
          and d.docstate = 3 -- статус документа
          and s.doctype = d.doctype
          and s.docID = d.ID
          and s.article = MyArticle;
      ELSE -- дял внутренних перемещений всех видов
        SELECT MAX(TO_CHAR(d.CREATEDAT, 'YYYYMMDD') || d.ID)
            INTO res_all
        FROM supermag.smdocuments d, supermag.smspec s
        WHERE d.opcode = MyOPCode
          and (d.userop = MyUserOp or MyUserOp is null)
          and d.createdat <= TRUNC(sysdate)
          and (d.locationfrom = MyStoreloc or d.locationto = MyStoreloc)
          and d.docstate = 3 -- статус документа
          and s.doctype = d.doctype
          and s.docID = d.ID
          and s.article = MyArticle;
      END IF;
               
        IF res_all IS NULL THEN
           RETURN NULL;   
        ELSE   
          RETURN(SUBSTR(res_all, 9, LENGTH(res_all) - 8));
        END IF;
       EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
      
END Article_LastDocID_from_DATE;
/
grant execute on supermag.Article_LastDocID_from_DATE to supermag_user;
commit;