--
prompt ******* Места хранения
--
CREATE OR REPLACE function USIODLSTORELOCATIONS return DummyTypeHolder.TRefCursor
is
    vSQL varchar2(4000);
    vResult DummyTypeHolder.TRefCursor;
begin
    vSQL := 'select'||
      ' l.Name LocName'||
      ',l.KPP'||
      ',l.GLN'||
      ',l.Address'||
      ',l.Tel'||
      ',l.Commentary'||
      ',decode(l.LocType,0,''Центральный склад'',1,''Склад возврата'',2,''Склад'',3,''Торговый зал'',4,''Склад-магазин'',5,''Склад брака'',-1,''Офис'',to_char(l.LocType)) LocTypeName'||
      ' from SMStoreLocations l'||
      ' where l.Accepted=''1'''||
      ' order by l.Name';
    open vResult for vSQL;
    return vResult;
end;
/
declare
 vID SAInfoObjects.ID%type := 'USIODLSTORELOCATIONS';
 vTitle SAInfoObjects.Title%type := 'Дикий Лось. Места хранения';
begin
 update SAInfoObjects set Title = vTitle where ID = vID;
 if SQL%ROWCOUNT=0 then
    insert into SAInfoObjects(ID, Title) values(vID, vTitle);
 end if;
 delete from SMInfoObjectsColumn where ObjectID = vID;
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('LocName'),'Наименование',1,'1','VARCHAR2',255,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('KPP'),'КПП',0,'0','VARCHAR2',9,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('GLN'),'GLN',0,'0','VARCHAR2',13,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('Address'),'Адрес',0,'0','VARCHAR2',255,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('Tel'),'Телефон',0,'0','VARCHAR2',40,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('Commentary'),'Комментарий',0,'0','VARCHAR2',255,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('LocTypeName'),'Тип места хранения',0,'1','VARCHAR2',20,null);
 commit;
end;
/
