Querys de tablas principales de SIM (Oracle Retail Store Inventory Management)

Querys-util-ORSIM.sql

--Tabla de Maestro de Artículos
select * from AS_ITM where id_itm='&codigoitem';

--Tabla Item / Location
select * from RK_ITM_LCN where id_itm='&codigoitem';

--Solicitudes de Inventario
select * from rk_item_request where item_request_id=&itemrequesid;
select * from RK_ITEM_REQUEST_LINE_ITEM where item_request_id=&itemrequesid; --Detalle de Solicitud de Inventario

-- Tabla de Proveedores de SIM
select * from pa_spr;

--Tabla Artículo / Proveedor
select * from RK_ITEM_SUPPLIER  where id_itm = '&codigoitem' and id_spr = '&idproveedor';

--Tabla Artículo / Proveedor /Region
select * from RK_ITEM_SUPP_COUNTRY where id_itm='&codigoitem' and id_spr='&idproveedor' and country_id='VE';

--Tabla Artículo / Proveedor / Manufactura
select * from item_supp_manufacture where item_id='&codigoitem' and supplier_ID='&idproveedor' and country_mfr_id='VE';

--Ordenes de Compra
select * from RK_ORDERS WHERE ORDER_ID='&idorden';

--StockOrder
select * from RK_ALLOCATIONS where allocation_id='&idallocation';

--Reaprovisionamiento en RMS a SIM (cantidad de articulos que faltan en SIM por enviar de RMS)
select * from item_loc il, repl_item_loc rpl 
 where il.loc = 1
   and il.item = rpl.item
   and rpl.location = il.loc
   and rpl.loc_type = il.loc_type
   and il.item in (select il_sim.id_itm
                         from as_itm_rtl_str@linksim il_sim -- @linksim: dblink desde base de datos de rms a base de datos de orsim
                        where il_sim.id_str_rt = il.loc
                          and il_sim.repn_type is null ); -- cambiando la condicion a IS NOT NULL sabremos cuales pasaron a SIM

--Precios entre SIM y RMS
SELECT   rfr.item,
         im.item_desc,
         rfr.location,
         rfr.action_date,
         rfr.simple_promo_retail rms_price,
         rfr.on_simple_promo_ind,
         TO_NUMBER(REPLACE (REPLACE (TO_CHAR (psim.rp_sls_crt), 'VEF'),',','.')) sim_price
  FROM   rpm_future_retail rfr, as_itm_rtl_str@linksim psim, item_master im -- @linksim: dblink desde base de datos de rms a base de datos de orsim
 WHERE       rfr.item = psim.id_itm
         AND im.item = rfr.item
         AND rfr.location = &idstore
         AND rfr.location = psim.id_str_rt
         AND rfr.simple_promo_retail <> TO_NUMBER(REPLACE (REPLACE (TO_CHAR (psim.rp_sls_crt), 'VEF'),',','.'))
         AND rfr.action_date =
               (SELECT   MAX (rfr2.action_date)
                  FROM   rpm_future_retail rfr2
                 WHERE   rfr2.item = rfr.item
                         AND rfr2.location = rfr.location
                         AND rfr2.action_date <= GET_VDATE );