gnats/712: problem with inv_util.lens_change_inv_stat query

[bug-gnats]

>Number:         712
>Category:       gnats
>Synopsis:       problem with  inv_util.lens_change_inv_stat query
>Confidential:   no
>Severity:       serious
>Priority:       medium
>Responsible:    unassigned
>State:          open
>Class:          support
>Submitter-Id:   net
>Arrival-Date:   Thu Dec 07 07:50:49 -0600 2006
>Originator:     Cindy Lannan
>Release:        
>Organization:
Support 
>Environment:
DLx W/P   2005.1.2   Lens 
>Description:
getting oracle alert error logs; just started this week.  Is this related to 
the recent install of Lens
The below error, look at 'Query Duration', is being caused by this partial part 
of a statement:

That query is from inv_util.lens_change_inv_stat, here is the full query, and 
can you check if there is any product PR with respect to this. If not we will 
have to create a product PR.

 

select liq.lpn         lpn,

              liq.lpn_par           lpn_par,

              liq.item_num       item_num,

              liq.lot_num          lot_num,

              liq.config            config,

              liq.qty     qty,

              liq.catch_qty     catch_qty,

              liq.wh_id             wh_id,

              liq.loc_num         loc_num,

              liq.inv_stat_txt  inv_stat_txt,

              0                        chg_typ,

              null                     ra_num,

              null                     rcp_num,

              null                     rcp_typ_txt

     from rcp r,

              wz,

              loc,

              lot l,

              lpn_dtl ld,

              lpn_inv_que liq

    where (r.ven_id                      = nvl(i_vendor, r.ven_id)

               or (r.ven_id is null

              and i_vendor is null))

              and r.rcp_num(+)         = ld.rcp_num

              and wz.wh_id              = loc.wh_id

              and wz.wz_id               = loc.wz_id

              and loc.wh_id              = liq.wh_id

              and loc.loc_num          = liq.loc_num

              and (liq.yard_loc_num = nvl(i_yard_loc_num, liq.yard_loc_num)

               or (liq.yard_loc_num is null

              and i_yard_loc_num is null))

              and (liq.vsl_id = nvl(i_vsl_id, liq.vsl_id)

               or (liq.vsl_id is null

              and i_vsl_id is null))

              and (liq.car_id = nvl(i_car_id, liq.car_id)

               or (liq.car_id is null

              and i_car_id is null))

              and (liq.bldg_num       = nvl(i_bldg_num, liq.bldg_num)

               or (liq.bldg_num is null

              and i_bldg_num is null))

              and (l.mfg_wh_id         = nvl(i_mfg_wh_id, l.mfg_wh_id)

               or (l.mfg_wh_id is null

              and i_mfg_wh_id is null))

              and (l.mfg_loc_num     = nvl(i_mfg_loc_num, l.mfg_loc_num)

               or (l.mfg_loc_num is null

              and i_mfg_loc_num is null))

              and l.lot_num              = liq.lot_num

              and l.item_num            = liq.item_num

              and liq.wh_id               = nvl(i_wh_id, liq.wh_id)

              and liq.loc_num           = nvl(i_loc_num, liq.loc_num)

              and liq.lpn           = 
decode(v_lpn,null,liq.lpn,decode(v_lpn_par,null,v_lpn,liq.lpn))

              and liq.lpn_par       = 
decode(v_lpn_par,null,liq.lpn_par,decode(v_lpn,null,v_lpn_par,liq.lpn_par))

              and liq.item_num         = nvl(i_item_num, liq.item_num)

              and liq.lot_num           = nvl(i_lot_num, liq.lot_num)

              and liq.config  = nvl(i_config, liq.config)

          and liq.inv_stat_txt  = nvl(i_inv_stat_txt,liq.inv_stat_txt)

              and (ld.rcp_typ_txt      = nvl(i_rcp_typ_txt, ld.rcp_typ_txt)

               or (ld.rcp_typ_txt is null

              and i_rcp_typ_txt is null))

              and (ld.ra_num            = nvl(i_ra_num, ld.ra_num)

               or (ld.ra_num is null

              and i_ra_num is null))

              and (ld.rcp_num          = nvl(i_rcp_num, ld.rcp_num)

               or (ld.rcp_num is null

              and i_rcp_num is null))

              and (liq.mfg_dt      >= nvl(to_date(i_mfg_dt_start, 
'YYYYMMDDHH24MISS'), liq.mfg_dt)

               or (liq.mfg_dt is null

              and i_mfg_dt_start is null)) 

              and (liq.mfg_dt      <= 
nvl(to_date(i_mfg_dt_end,'YYYYMMDDHH24MISS'), liq.mfg_dt)

               or (liq.mfg_dt is null

              and i_mfg_dt_end is null))

              and ld.lpn                   = liq.lpn

              and ld.item_num          = liq.item_num

              and ld.lot_num            = liq.lot_num

              and ld.config              = liq.config

        UNION

   select it.lpn            lpn,

          it.lpn            lpn_par,

          it.item_num       item_num,

          it.lot_num        lot_num,

          it.config         config,

          it.qty            qty,

          0                 catch_qty,

          it.wh_id          wh_id,

          null              loc_num,

          it.inv_stat_txt   inv_stat_txt,

          1                 chg_typ,

          null              ra_num,

          null              rcp_num,

          null              rcp_typ_txt

     from rcp r, 

              lot l,

          in_transit it

    where (r.ven_id            = nvl(i_vendor, r.ven_id)

               or (r.ven_id is null

              and i_vendor is null))

      and r.rcp_num(+)       = it.rcp_num

      and (l.mfg_wh_id    = nvl(i_mfg_wh_id, l.mfg_wh_id)

       or (l.mfg_wh_id is null

      and i_mfg_wh_id is null))

      and (l.mfg_loc_num  = nvl(i_mfg_loc_num, l.mfg_loc_num)

       or (l.mfg_loc_num is null

      and i_mfg_loc_num is null))

      and l.lot_num       = it.lot_num

      and l.item_num      = it.item_num

      and it.item_num     = i_item_num

      and it.lot_num      = i_lot_num

      and (it.inv_stat_txt = nvl(i_inv_stat_txt,it.inv_stat_txt)

       or (it.inv_stat_txt is null

      and  i_inv_stat_txt is null))

      and it.lpn          = nvl(i_lpn, it.lpn)

      and (it.vsl_id       = nvl(i_vsl_id, it.vsl_id)

       or (it.vsl_id is null

      and i_vsl_id is null))

      and (it.car_id       = nvl(i_car_id, it.car_id)

       or (it.car_id is null

      and i_car_id is null))

      and (it.wh_id        = nvl(i_wh_id, it.wh_id)

       or (it.wh_id is null

      and i_wh_id is null))

      and it.config       = nvl(i_config, it.config)

      and (it.mfg_dt      >= nvl(to_date(i_mfg_dt_start, 'YYYYMMDDHH24MISS'), 
it.mfg_dt)

      or (it.mfg_dt is null

      and i_mfg_dt_start is null))

      and (it.mfg_dt      <= nvl(to_date(i_mfg_dt_end, 'YYYYMMDDHH24MISS'), 
it.mfg_dt)

      or (it.mfg_dt is null

      and i_mfg_dt_end is null))

      and (it.rcp_num     = nvl(i_rcp_num, it.rcp_num)

      or (it.rcp_num is null

      and i_rcp_num is null))

      and (it.ra_num      = nvl(i_ra_num, it.ra_num)

      or (it.ra_num is null

      and i_ra_num is null))

      and (it.rcp_typ_txt = nvl(i_rcp_typ_txt, it.rcp_typ_txt)

      or (it.rcp_typ_txt is null

      and i_rcp_typ_txt is null))

    UNION

   select rld.lpn              lpn,

              rld.lpn             lpn_par,

              rld.item_num               item_num,

              rld.lot_num                  lot_num,

              rld.config                    config,

              rld.ps_line_qty            qty,

              rld.ps_catch_qty         catch_qty,

              r.wh_id                        wh_id,

              null                             loc_num,

              rld.inv_stat_txt            inv_stat_txt,

              2                                chg_typ,

          rld.ra_num            ra_num,

          rld.rcp_num           rcp_num,

          rld.rcp_typ_txt       rcp_typ_txt

    from lot l,

             rcp r,

             rcp_lpn_dtl rld

   where (l.mfg_wh_id                 = nvl(i_mfg_wh_id, l.mfg_wh_id)

      or (l.mfg_wh_id is null

     and i_mfg_wh_id is null))

     and (l.mfg_loc_num              = nvl(i_mfg_loc_num, l.mfg_loc_num)

      or (l.mfg_loc_num is null

     and i_mfg_loc_num is null))

     and (r.ven_id                        = nvl(i_vendor, r.ven_id)

      or (l.ven_id is null

     and i_vendor is null))

     and l.lot_num                       = rld.lot_num

     and l.item_num             = rld.item_num

     and (r.asn_car_id                  = nvl(i_car_id, r.asn_car_id)

      or (r.asn_car_id is null

     and i_car_id is null))

     and (r.asn_vsl_id                  = nvl(i_vsl_id, r.asn_vsl_id)

      or (r.asn_vsl_id is null

     and i_vsl_id is null))

     and r.rcp_num                      = rld.rcp_num

     and rld.lpn               = nvl(i_lpn, rld.lpn)

     and rld.item_num                  = i_item_num

     and rld.lot_num                    = i_lot_num

     and rld.config             = nvl(i_config, rld.config)

     and (rld.expinv_stattxt     = nvl(i_inv_stat_txt,rld.expinv_stattxt)

      or (rld.expinv_stattxt is null

     and i_inv_stat_txt is null))

     and (rld.mfg_dt           >= nvl(to_date(i_mfg_dt_start, 
'YYYYMMDDHH24MISS'), rld.mfg_dt)

      or (rld.mfg_dt is null

    and i_mfg_dt_start is null))

     and (rld.mfg_dt               <= nvl(to_date(i_mfg_dt_end, 
'YYYYMMDDHH24MISS'), rld.mfg_dt)

      or (rld.mfg_dt is null

     and i_mfg_dt_end is null))

     and rld.rcp_num                   = nvl(i_rcp_num, rld.rcp_num)

     and rld.rcp_typ_txt   = nvl(i_rcp_typ_txt, rld.rcp_typ_txt)

     and rld.ps_line_qty   > 0

     and nvl(rld.rcv_qty,0)            = 0

     and nvl(rld.dmg_qty,0)          = 0)

 

 

 

Rajesh Lalgudi (Raj) 
Project Leader 
>How-To-Repeat:
>Fix:
Unknown
>Notify-List:    
>Unformatted: