Wiki

New Case Case Status Kiln
Log In

Wiki

 
Classification  Report Query - Validation Logi…
  
Page Collections
Attributes
Hierarchy
  • RSS Feed

Last modified on 5/12/2017 12:48 PM by User.

Tags:

Report Query - Validation Logic

This article is my attempt to put together some of the logic for most common REPORT_QUERY validations run in PUP/AutoPUP. 

----------------------------------------------------------------------------------------------------------------------------------------------------

Invalid Core Code For Line Code; 

Category
New Parts
Future Changes 

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'Invalid Core Code for Line Code' as message   
FROM bulletin b     
INNER JOIN transaction_group g ON g.group_id = b.group_id     
INNER JOIN transaction_key k ON g.group_id = k.group_id   
INNER JOIN part_transaction p ON p.transaction_id=k.transaction_id    
INNER JOIN dbo_classificationdata.hq_line l ON l.hq_abbr = k.hq_abbr 
INNER JOIN dbo_classificationdata.part_master pm ON Pm.hq_abbr = k.hq_abbr AND pm.part_number = k.part_number 
LEFT JOIN dbo_classificationdata.hq_line_code lc ON lc.parent_hq_abbr = l.parent_hq_abbr AND lc.line_code = pm.line_code AND (lc.core_code = p.core_code OR p.core_code =0)  
WHERE p.core_code IS NOT NULL AND  lc.line_code IS NULL 
AND k.hq_abbr NOT IN ('MWA', 'MWP', 'VMH', 'VMM')  
AND b.bulletin_id ={ID}

Invalid Line Code for Line; 

Category
New Parts
Future Changes 

1:
2:
3:
4:
5:
6:
7:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'Invalid Line Code for Line' as message 
FROM bulletin b   
INNER JOIN transaction_group g ON g.group_id = b.group_id   
INNER JOIN transaction_key k ON g.group_id = k.group_id 
INNER JOIN part_transaction p ON p.transaction_id=k.transaction_id   
WHERE  p.line_code NOT IN (SELECT line_code FROM dbo_classificationdata.line_code_v lc INNER JOIN dbo_classificationdata.hq_line l ON l.tams_abbr = lc.tams_abbr 
WHERE l.hq_abbr=k.hq_abbr)  AND b.bulletin_id ={ID}

Duplicate New Part; 

Category
New Parts 

1:
2:
3:
4:
5:
6:

SELECT  k.hq_abbr,k.part_number as part_number, 'Duplicate New Part' as message 
FROM bulletin b 
INNER JOIN transaction_group g ON g.group_id = b.group_id 
INNER JOIN transaction_key k ON g.group_id = k.group_id WHERE b.bulletin_id ={ID} 
GROUP BY  bulletin_number, k.hq_abbr, k.part_number
HAVING ( COUNT(*) > 1 )

Missing Physical Properties;

Category
New Parts 

1:
2:
3:
4:
5:
6:
7:
8:
9:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'Physical Properties are Required (Weight, Dimensions)' as message  
FROM bulletin b    
INNER JOIN transaction_group g ON g.group_id = b.group_id   
INNER JOIN transaction_key k ON g.group_id = k.group_id    
INNER JOIN part_transaction pt ON pt.transaction_id = k.transaction_id   
INNER JOIN class_transaction ct ON ct.transaction_id = k.transaction_id  
WHERE  (weight_kilos IS NULL OR weight_kilos =0 OR pog_depth =0 OR pog_height =0 OR pog_width = 0) 
AND class NOT IN ('NR', 'Z')   
AND b.bulletin_id ={ID}

Dimensions appear incorrect;

Category
New Parts 

1:
2:
3:
4:
5:
6:
7:
8:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'Dimensions appear to be incorrect unit' as message  FROM bulletin b    
INNER JOIN transaction_group g ON g.group_id = b.group_id  
INNER JOIN transaction_key k ON g.group_id = k.group_id    
INNER JOIN part_transaction pt ON pt.transaction_id = k.transaction_id   
INNER JOIN class_transaction ct ON ct.transaction_id = k.transaction_id  
WHERE  (pog_depth + pog_height+ pog_width) <  50  
AND class NOT IN ('NR', 'Z') 
AND b.bulletin_id ={ID}

New Part already exists in Part Master

Category
New Parts 

1:
2:
3:
4:
5:
6:
7:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'New Part already exists in Master' as message 
FROM bulletin b   
INNER JOIN transaction_group g ON g.group_id = b.group_id   
INNER JOIN transaction_key k ON g.group_id = k.group_id 
WHERE part_number  IN 
(SELECT part_number FROM dbo_ClassificationData.part_master WHERE hq_abbr = k.hq_abbr)   
AND b.bulletin_id ={ID}

Missing Pricing;

  1. DC Cost
  2. Blue Price
  3. Gold Price
  4. List Price
  5. Red Price
  6. Green Price
  7. M.I Price
Category
New Parts 

1:
2:
3:
4:
5:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'Missing Blue Price' as message FROM bulletin b 
INNER JOIN transaction_group g ON g.group_id = b.group_id 
INNER JOIN transaction_key k ON g.group_id = k.group_id 
WHERE transaction_id not in (SELECT transaction_id FROM price_transaction p WHERE p.transaction_id=k.transaction_id AND p.sheet_id=1)  
AND b.bulletin_id ={ID}

Price Increment Checks;

  • Main goal is to make sure that prices incrementing and not getting cheaper across increasing price sheets.
Category
New Parts 

1:
2:
3:
4:
5:
6:
7:
8:
9:

SELECT  k.hq_abbr, k.ext_part_number as part_number,  'Price Error:'||high.sheet_name||'<'||low.sheet_name as message 
FROM bulletin b   
INNER JOIN transaction_group g ON g.group_id = b.group_id   
INNER JOIN transaction_key k ON g.group_id = k.group_id 
INNER JOIN part_transaction pt ON pt.transaction_id=k.transaction_id  
INNER JOIN price_transaction p1 ON pt.transaction_id=p1.transaction_id  
INNER JOIN price_transaction p2 ON p1.transaction_id=p2.transaction_id and pt.transaction_id=p2.transaction_id  
INNER JOIN price_order ON p1.sheet_id = higher_sheet_id and p2.sheet_id = lower_sheet_id  
INNER JOIN dbo_ClassificationData.price_sheets high ON p1.sheet_id=high.sheet_id  INNER JOIN dbo_ClassificationData.price_sheets low ON p2.sheet_id=low.sheet_id  WHERE p1.item_value<p2.item_value  AND b.bulletin_id ={ID}

Red/Green Price Rounding Check;

Category
New Parts 

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

SELECT   k.hq_abbr, k.ext_part_number as part_number, 'Red/Green Price Rounding Check' as message
FROM DBO_CLASS_TRANSACTION.bulletin b 
INNER JOIN DBO_CLASS_TRANSACTION.transaction_group g ON g.group_id = b.group_id 
INNER JOIN DBO_CLASS_TRANSACTION.transaction_key k ON g.group_id = k.group_id 
INNER JOIN DBO_CLASS_TRANSACTION.part_transaction pt ON pt.transaction_id=k.transaction_id 
INNER JOIN DBO_CLASS_TRANSACTION.price_transaction green ON green.transaction_id = pt.transaction_id AND green.sheet_id = 10
INNER JOIN DBO_CLASS_TRANSACTION.price_transaction red ON red.transaction_id = pt.transaction_id  AND red.sheet_id = 8
WHERE ((red.item_value <100.00 AND (SUBSTR(TO_CHAR(red.item_value),-1,1) <> 9) and (INSTR(red.item_value,'.') > 0) and ((INSTR(red.item_value,'.') + 2) = LENGTH(red.item_value)))
OR (green.item_value <100.00 AND (SUBSTR(TO_CHAR(green.item_value),-1,1) <> 9) and (INSTR(green.item_value,'.') > 0) and ((INSTR(green.item_value,'.') + 2) = LENGTH(green.item_value)))
OR (green.item_value >100.00 AND (SUBSTR(TO_CHAR(green.item_value),-2,2) <> 99) and (INSTR(green.item_value,'.') > 0) and ((INSTR(green.item_value,'.') + 2) = LENGTH(green.item_value)))
OR (red.item_value >100.00 AND (SUBSTR(TO_CHAR(red.item_value),-2,2) <> 99) and (INSTR(red.item_value,'.') > 0) and ((INSTR(green.item_value,'.') + 2) = LENGTH(red.item_value))))
and b.bulletin_id ={ID}

Missing Harmonize Code;

Category
New Parts 

1:
2:
3:
4:
5:
6:
7:
8:

SELECT  k.hq_abbr, k.ext_part_number as part_number, 'Harmonize Code is Required' as message FROM bulletin b  
INNER JOIN transaction_group g ON g.group_id = b.group_id  
INNER JOIN transaction_key k ON g.group_id = k.group_id  
INNER JOIN part_transaction pt ON pt.transaction_id = k.transaction_id 
INNER JOIN class_transaction ct ON ct.transaction_id = k.transaction_id  
WHERE  (harmonize_code IS NULL OR harmonize_code ='') 
AND class NOT IN ('NR', 'Z') 
AND b.bulletin_id ={ID}