Overview

You may want to have a list of movements in WARN status that are holding stock for a specific product.

Solution

Please reach out to the support team with stock item specifications and they will get back to you with a list of required WARN movements.

<supportagent>

A query such as the following can be used to find movements in WARN status holding a specific stock item:

Note: You will need to adjust this query according to the specifications of the product shared by the customer.

-- Get the sum of warn movements going into or out of this bin.

select a.gtrans_num,
nvl(sum(b.qty1), 0), nvl(sum(b.qty2), 0), nvl(sum(b.qty3), 0),
nvl(sum(b.qty4), 0), nvl(sum(b.qty5), 0), nvl(sum(b.qty6), 0),
nvl(sum(b.qty7), 0), nvl(sum(b.qty8), 0), nvl(sum(b.qty9), 0),
nvl(sum(b.qty10),0), nvl(sum(b.qty11),0), nvl(sum(b.qty12),0)
from pro.gtrans_defs a, pro.gtrans_items b
where a.gtrans_num = b.gtrans_num
and a.gtrans_state = 'WARN'
and decode('OUT', 'IN', a.gdest_type, a.gsource_type) = 'RETAIL-STOCK'
and decode('OUT', 'IN', a.gdest_num, a.gsource_num) = '90020'
and decode('OUT', 'IN', b.gdest_bin, b.gsource_bin) =  'CLP001'
and b.season = 'S191'
and b.sty_num = 'MP01'
and b.sty_qual = '61845'
and b.bf_mat_char_val = '2BLU-BLUE'
and b.sty_size =  '3RG'
and nvl(b.gitem_state, '~') not in ('P','B')
group by a.gtrans_num
having nvl(sum(b.qty4), 0) > 0 

The output of the query would be such as the following:

After running the query, you would see that the specified product is included in some movements with WARN status. Share this list of WARN movements with the customer.

</supportagent>