--
prompt ******* Карточки товаров. Остатки. Цены
--
CREATE OR REPLACE function USIODLCARDSINFO(pLocID in SMStoreLocations.ID%type) return DummyTypeHolder.TRefCursor
is
    vSQL varchar2(4000);
    vResult DummyTypeHolder.TRefCursor;
begin
    vSQL := 'select'||
      ' a.Article'||
      ',nvl((select p.Price from SMLocPrices l, SMPrices p'||
      ' where l.PriceType=p.PriceType and l.LocID=p.StoreLoc and bitand(l.Flags,2)!=0 and p.Article=a.Article and p.StoreLoc='||to_char(pLocID)||
      '),0) CashPrice'||
      ',nvl((select p.Price from SMLocPrices l, SMPrices p'||
      ' where l.PriceType=p.PriceType and l.LocID=p.StoreLoc and bitand(l.Flags,4)!=0 and p.Article=a.Article and p.StoreLoc='||to_char(pLocID)||
      '),0) InternetPrice'||
      ',nvl(g.Quantity,0) Quantity'||
      ',nvl(g.Quantity+g.FoundQuantity-g.ReservedQuantity-g.IncomingQuantity,0) FreeQuantity'||
      ',nvl(g.Quantity+g.FoundQuantity-g.ReservedQuantity-g.IncomingQuantity,0)-nvl(o.Quantity,0) FreeQuantityWithOper'||
      ' from SVCardName a'||
      ',SMGoods g'||
      ',(select o.Article, sum(o.SaleQuantity-o.ReturnQuantity) Quantity'|| 
      ' from SMOperGoods o'||
      ' where o.LocID='||to_char(pLocID)||
      ' group by o.Article'||
      ') o'||
      ' where a.Accepted=1'||
      ' and g.Article(+)=a.Article'||
      ' and g.StoreLoc(+)='||to_char(pLocID)||
      ' and o.Article(+)=a.Article'||
      ' order by a.Article';
    open vResult for vSQL;
    return vResult;
end;
/
declare
 vID SAInfoObjects.ID%type := 'USIODLCARDSINFO';
 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('Article'),'Артикул',1,'1','VARCHAR2',50,null);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('CashPrice'),'Цена для кассы',0,'1','NUMBER',19,4);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('InternetPrice'),'Цена для интернет-магазина',0,'1','NUMBER',19,4);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('Quantity'),'Текущий остаток',0,'1','NUMBER',14,3);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('FreeQuantity'),'Доступный остаток',0,'1','NUMBER',14,3);
 insert into SMInfoObjectsColumn(ObjectID,Column_name,Col_comment,IsPK,IsNotNull,Data_type,Data_length,Data_scale)
 values (vID,upper('FreeQuantityWithOper'),'Оперативно доступный остаток',0,'1','NUMBER',14,3);
 commit;
end;
/
