General Reference Queries
Note: These are day-to-day lookup queries for process tracking, item/hierarchy research, and supplier verification. For queries specific to the item re-class workflow, see the Item Re-Class Process Query Index below.
1. Process & Staging Verification
Check Today's Process Tracker Actions by User (Notification check — confirms what a user has submitted today.)
alter session set nls_date_format = 'mm/dd/yyyy HH:MI:SS';
select *
from rms.svc_process_tracker
where USER_ID = 'ANDERSDA'
and trunc(action_date) = trunc(sysdate) order by action_date desc;
Check Staging Table Status by Process ID (Returns the count of records by process$status for a given Process ID.)
select process$status, count (1)
from rms.svc_item_master
where process_id = '21006523'
group by process$status;
Find Error Messages by Process ID (Returns readable error text for a failed Process ID. The commented block can be used instead to search by RIN / VPN / transaction number.)
select r.rtk_text,
replace(c.error_msg,'@0','') error_message,
c.*
from rms.coresvc_item_err c, rms.rtk_errors r where REGEXP_SUBSTR(replace(C.ERROR_MSG,'@0',''), '[^@]*') = R.RTK_KEY (+)
and process_id in ('21043093')
order by process_id desc, rtk_text;
Check Item/Location Staging Table (Confirms today's item/location records submitted by a given user (step 2 check).)
select *
from RMS.NEX_SVC_ITEM_LOC
where created_by = 'sobalvk'
and trunc(created_date) = trunc(sysdate) order by created_date desc;
Check if Item Is Already in the Item/Supplier/Country Staging Table
select *
from rms.svc_item_supp_country_dim
where item in ('14508509');
2. Item & Hierarchy Lookups
Look Up Item Hierarchy for a New Add Transaction (Use this when users submit an add transaction — returns the parent, transaction item, hierarchy, color, and size group.)
SELECT IM.item_grandparent as "PARENT", IM.item_parent as "TRANSACTION", IM.dept, IM.class, IM.subclass, IM.diff_1 as color,
XIM.diff_2 as size_group_id, IM.diff_2 as "SIZE", IM.item_desc_secondary, IM.create_datetime, IM.create_id
FROM RMS.item_master IM, (SELECT item, diff_2 FROM RMS.item_master) XIM
WHERE IM.item_grandparent = XIM.item
and IM.item_GRANDparent in ('16863505')
ORDER BY IM.dept, IM.class, IM.subclass, IM.diff_1, XIM.diff_2 asc;
Look Up Item by GTIN (Returns the item parent and primary reference item indicator for a GTIN.)
Select*
from rms.item_master
where item in ('196985578839');
Check Dept / Class / Subclass Relationship
Select *
from rms.subclass
where dept = '770';
Look Up SKULIST Detail
SELECT*
FROM RMS.skulist_detail
WHERE SKULIST IN ('21240112','21240110','21240111');
Count Total Locations Attached to an Item
select item, count(loc)
from rms.item_loc
where item in ('19437854')
group by item
order by item asc;
Range Already-Built Items to a Destination Dept (e.g., 781) Check (Pulls web description, brand, MSRP, VPN, supplier color, and e-comm UDA values for items being ranged into an already-built dept/class/subclass.)
SELECT DISTINCT im.item_grandparent AS PARENT, im.item_parent AS TRANSACTION, im.dept, xim.item_desc_secondary AS WEB_DESC, xim.brand_name AS BRAND, xim.MFG_REC_RETAIL AS MSRP, iss.VPN AS VPN, iss.SUPP_DIFF_1 AS SUPPLIER_COLOR, uil.UDA_ID AS ECOMM_UDA, uil.UDA_VALUE AS ECOMM_UDA_VALUE
FROM rms.item_master im JOIN rms.item_supplier iss ON im.item_parent = iss.item
LEFT JOIN rms.uda_item_lov uil ON im.item_parent = uil.item
AND uil.UDA_ID IN ('9630', '9631', '9632', '9627')
JOIN (SELECT item, brand_name, mfg_rec_retail, item_desc_secondary FROM rms.item_master) xim
ON xim.item = im.item_parent
WHERE im.item_grandparent IN ('19507468','19507466','19507467')
AND iss.primary_supp_ind = 'Y'
ORDER BY im.dept, im.item_grandparent, im.item_parent ASC, uil.uda_value DESC;
3. Supplier, Vendor & Costing Checks
Show Primary Supplier at the Location Level (Manual maintenance check — the commented lines show how to swap in a SKULIST or LOC_LIST instead of a single item/location.)
select item, loc, primary_supp, status
from rms.item_loc
where item in ('11982497')
and loc in ('505');
Confirm a Supplier Is Attached to an Item
select item, supplier, vpn
from rms.item_supplier
where item in ('19507468','19507466','19507467')
and supplier in ('187949904');
Identify Items Missing the Correct Primary Supplier (Cross-checks a SKULIST against a LOC_LIST to find item/location combinations where the primary supplier doesn't match what's expected.)
select sd.skulist, sd.item, lld.loc_list, il.loc, il.primary_supp, il.store_ord_mult, il.source_method, il.source_wh, il.last_update_datetime, il.last_update_id
from RMS.SKULIST_DETAIL sd, RMS.LOC_LIST_DETAIL lld, rms.item_loc il
where sd.item = il.item
and il.loc = LLD.LOCATION
and sd.skulist in ('20740102')
and lld.loc_list in ('109220')
and il.primary_supp <>'91796839'
order by sd.skulist, lld.location asc;
Confirm a Supplier Exists in RMS
Select *
from rms.sups
where supplier = '107257560';
Check Default Unit of Purchase (UOP)
Select item, supplier, default_uop, create_datetime, last_update_id, last_update_datetime
from rms.item_supp_country
where item in ('13284518')
and supplier in ('9207775','8086');
Look Up Primary Supplier — Single Item, Item List, or Loc List (General-purpose template. Swap in the commented lines to search by SKULIST or LOC_LIST instead of a single item/location.)
select item, loc, primary_supp, status
from rms.item_loc
where item in ('')
and loc in ('746')
;
Check Item/Supplier/Country Record
select*
from rms.item_supp_country
where item in ('7465374');
Look Up Vendor & Transaction Details by GTIN
select distinct TRUNC(IM.CREATE_DATETIME) CREATE_DATE, im.dept DEPT, s.supplier VENDOR, s.sup_name VENDOR_NAME, im.item_grandparent PARENT, im.ITEM_DESC_SECONDARY PARENT_NAME, iss.vpn VPN, im.item_parent TRANSACTION, IM.ITEM_DESC TRANSACTION_NAME, im.item GTIN, IM.STATUS
from rms.item_master im, rms.item_supplier iss, rms.sups s
where im.item_parent = iss.item
and iss.supplier = s.supplier
and im.item in ('847280052899')
and iss.primary_supp_ind = 'Y'
order by im.item_grandparent, im.item_parent asc;
Check Item/Supplier/Country Dimension Record (Same as the staging check above (G5), but against the live table rather than the staging table.)
select *
from rms.item_supp_country_dim
where item in ('14508609');
Item Re-Class Process Query Index
Note: These queries are utilized during the item re-class sequence. They are centrally managed within the Item Re-class working directory.
1. Overview (Section 2.2.1.8)
Find UDAs attached to a Skulist (Mandatory UDAs Step 2)
SELECT DISTINCT sd.item, ul.uda_id, ul.uda_value
FROM rms.uda_item_lov ul, rms.skulist_detail sd
WHERE ul.item = sd.item
AND sd.skulist = '';
2. Forecasted Items on Re-Classes (Section 2.2.2)
Check if Departments are forecastable
SELECT DISTINCT dept, forecast_ind
FROM rms.item_master
WHERE forecast_ind = 'Y'
AND Dept IN ('');
Determine if an item is on forecasting (Must match the destination Dept.)
SELECT DISTINCT ril.dept, ril.item, item_desc
FROM rms.repl_item_loc ril, rms.item_master im
WHERE ril.item = im.item
AND ril.item IN ('')
AND forecast_ind = 'Y'
AND (deactivate_date IS NULL OR deactivate_date > sysdate);
Determine if an item list is on forecasting (The forecastability of the item must match the destination Dept.)
SELECT sd.skulist, im.ITEM_PARENT, im.item_grandparent, im.DEPT, im.class, im.subclass, im.item_desc, im.forecast_ind
FROM rms.skulist_detail sd, rms.item_master im
WHERE sd.item = im.item_parent
AND sd.skulist IN ('')
AND im.forecast_ind = 'Y';
Identify domains attached to forecastable departments
SELECT *
FROM rms.domain_dept
WHERE dept IN ();
3. Components of Pack Items on Order & Error Resolution (Section 2.2.3 & 2.4)
Identifying component items on order (POs on this list will need to be closed)
SELECT *
FROM rms.ordhead oh, rms.ordsku os, rms.packitem p
WHERE oh.order_no = os.order_no
AND oh.status = 'A'
AND p.pack_no = os.item
AND EXISTS (
SELECT 'x'
FROM rms.packitem p, rms.item_master im
WHERE p.pack_no = os.item
AND (im.item = '' OR im.item_parent = '' OR im.item_grandparent = '')
AND p.item = im.item
);
4. Identifying Open Orders for Item Profiles & Item Lists (Section 2.3.1 & 2.5.1)
Find items on an open order that is partially received
SELECT oh.order_no, oh.order_type, oh.status, ol.item, oh.written_date, oh.NOT_AFTER_DATE, ol.location, ol.qty_ordered, ol.qty_received, ol.last_received
FROM rms.ordloc ol, rms.ordhead oh
WHERE oh.order_no = ol.order_no
AND ol.item IN (SELECT item_parent FROM rms.item_master WHERE item_grandparent IN ('12385927'))
AND oh.status <> 'C'
AND ol.qty_received IS NOT NULL
AND ol.qty_ordered > ol.qty_received
ORDER BY order_no DESC;
Check deals tables (Check BOTH queries below using PO numbers from step Q7)
SELECT *
FROM rms.deal_calc_queue
WHERE order_no IN ('');
SELECT *
FROM rms.deal_order_temp
WHERE order_no IN ('')
ORDER BY order_no ASC;
Check for open appointments on POs (Appointments must be closed before closing a PO)
SELECT DISTINCT ad.doc, ah.loc, ah.appt rms_appt_nbr, ah.status,
DECODE(ah.status, 'SC', 'Scheduled',
'AR', 'Arrived',
'AC', 'Closed',
'UNKNOWN') AS appt_status
FROM rms.appt_head ah, rms.appt_detail ad
WHERE ad.appt = ah.appt
AND ad.doc IN ('');
5. Resolving Re-Class Errors (Section 2.4)
Find a recent rejection (By user name or item; only tracks previous batch errors)
SELECT *
FROM rms.mc_rejections
WHERE user_id = 'NEXADID';
Find a historical rejection (By User, date, or item)
SELECT *
FROM rms.mc_rejections_archive
WHERE change_type = 'M';
AND user_id = 'NEXADID';
Validate if Item List / SKULIST re-classed
SELECT sd.skulist, im.ITEM_PARENT, im.item_grandparent, im.DEPT, im.class, im.subclass, im.item_desc
FROM rms.skulist_detail sd, rms.item_master im
WHERE sd.item = im.item_parent
AND sd.skulist IN ('');
Validate if a single item re-classed
SELECT item_parent, item_desc, dept, class, subclass
FROM rms.item_master
WHERE item IN ('');
6. Lawson Department Re-Classes (Section 4.1.2)
Check for a department move by Item List (Can catch mis-keyed items from an outside department)
SELECT DISTINCT dept
FROM rms.skulist_detail sd, rms.item_master im
WHERE sd.item = im.item_parent
AND sd.skulist = '';
Check for financial moves by Departments
SELECT g.group_no, g.group_name, d.dept, d.dept_name, d.group_no
FROM rms.groups g, rms.deps d
WHERE g.group_no = d.group_no
AND d.dept IN ();
7. Nightly Batch Communication to RAVE (Section 6.1)
Check re-classes scheduled to run in the nightly batch
SELECT *
FROM rms.reclass_head
ORDER BY reclass_no ASC;
Identify re-class effective dates / submission verification
SELECT *
FROM rms.reclass_head
WHERE reclass_no IN ();
Compare item hierarchies in v16 and v10 (Must be executed the following week)
SELECT DISTINCT im.item_parent, ri.item, im.dept, im.class, im.subclass,
COALESCE(vim.item_parent, vim.item) AS Highest_level_RIN,
vim.item, vim.dept, vim.class, vim.subclass
FROM rms.nex_reclass_item ri, rms.item_master im, rms_v10.item_master vim
WHERE ri.item = im.item
AND ri.item = vim.item
AND im.item = vim.item
AND reclass_date >= 'DD-MMM-YY'
AND reclass_date <= 'DD-MMM-YY'
AND im.item_level = '2';
8. Additional Queries
Item count per SKULIST (Target range is between 100-150 items for optimal performance)
SELECT skulist, COUNT(item)
FROM rms.skulist_detail
WHERE skulist IN ('')
GROUP BY skulist;