常用SQL2(底层修改库存数据)

yusheng

set termout     off
set feedback    0
set newpage     0
set verify      off
set underline   on
set echo        off
set heading     on
set linesize    162
set pagesize    60
set pause       off
ttitle          off
btitle          off
repheader       off



spool ic_loct_v11.lis



whenever sqlerror continue;
drop table ic_loct_inv_sts;
drop table ic_loct_inv_tmp;
whenever sqlerror exit rollback;
column sort_1 noprint



set termout on
prompt
prompt  Recalculation of Location Inventory (as seen in Item Inquiry)
prompt  Enter applicable Item Number; no value will recalculate ALL
prompt



-- for scheduled recalculation uncomment next line and comment line starting with 'accept appl_item'
--define appl_item = &1



-- when usage is single Item uncomment next line and comment line starting with 'define appl_item'
accept appl_item prompt 'Enter applicable Item Number or ALL: '



prompt
set termout     off



column  items noprint new_value which_item_id



select ' = 0' items -- in case next select result in nothing
from   dual;



select ' >= 0' items
from   dual
where  '&appl_item' IS NULL -- no value entered
or     upper('&appl_item') = 'ALL' -- value ALL entered
union     
select ' = '||item_id
from   ic_item_mst
where  item_no = upper('&appl_item') -- an Item Number entered
/



/*
Create Temporary Location Inventory table
*/



create table ic_loct_inv_tmp
(      item_id number(10) not null
,      whse_code varchar2(4) not null
,      lot_id number(10) not null
,      location varchar2(16) not null
,      loct_onhand number not null
,      loct_onhand2 number null
,      lot_status varchar2(4) null
,      qchold_res_code varchar2(4) null)
;



/*
Calculate Location Onhand and Location Onhand2
*/



insert into ic_loct_inv_tmp
select t.item_id
,      t.whse_code
,      t.lot_id
,      t.location
,      sum(t.trans_qty)
,      sum(t.trans_qty2)
,      NULL --,     t.lot_status
,      NULL --,     l.qchold_res_code
from   ic_item_mst i
--,      ic_lots_sts l
,      ic_tran_cmp_vw1 t
where  t.item_id &which_item_id
and    t.doc_type <> 'STSR'
and    t.doc_type <> 'STSI'
and    t.doc_type <> 'GRDI'
and    t.doc_type <> 'GRDR'
and    t.trans_qty <> 0
and    i.item_id = t.item_id
and    i.noninv_ind = 0
--and    l.lot_status = t.lot_status
group by t.item_id
,      t.whse_code
,      t.lot_id
,      t.location
--,      t.lot_status
--,      l.qchold_res_code
;



/*
Delete records where
- Location Onhand being 0
- or a (very) small quantity in both Location Onhand and Location Onhand 2
(often caused through roundings)
*/
DELETE FROM ic_loct_inv_tmp t
WHERE  ((ABS(loct_onhand) <= 0.000000001)
OR     (loct_onhand = 0))
AND item_id NOT IN (SELECT item_id
                    FROM ic_tran_cmp_vw1 c
                    WHERE doc_type = 'XFER'
   AND t.whse_code = c.whse_code
   AND t.lot_id = c.lot_id
   AND t.location = c.location);



/*
Create index; out of performance reasons
*/



create index ic_loct_inv_tmpi1
on ic_loct_inv_tmp
(      item_id
,      whse_code
,      lot_id
,      location
)
;



/*
Give all records a default Lot Status
*/



update ic_loct_inv_tmp l
set    lot_status =
       (select lot_status
       from    ic_item_mst m
       where   m.item_id = l.item_id)
where exists
  (select NULL
  from    ic_item_mst m2
  where   m2.item_id = l.item_id)
;



/*
Create Temporary Location Inventory Lot Status table
*/



create table ic_loct_inv_sts
(     item_id number(10) not null
,     whse_code varchar2(4) not null
,     lot_id number(10) not null
,     location varchar2(16) not null
,     lot_status varchar2(4) null)
;



/*
Select last applicable Lot Status
*/



insert into ic_loct_inv_sts
select item_id
,      whse_code
,      lot_id
,      location
,      lot_status
from   ic_tran_cmp
where  trans_id in
       (select max(trans_id)
       from   ic_tran_cmp t
       ,      ic_loct_inv_tmp l
       where  t.item_id = l.item_id
       and    t.whse_code = l.whse_code
       and    t.location = l.location
       and    t.lot_id = l.lot_id
       and    t.doc_type||'' in ('STSR','STSI','TRNI','TRNR')
       and    t.lot_id <> 0
       group by t.item_id
       ,    t.whse_code
       ,    t.location
       ,    t.lot_id)
;



/*
Create index; out of performance reasons
*/



create index ic_loct_inv_stsi1
on     ic_loct_inv_sts
(      item_id
,      whse_code
,      lot_id
,      location)
;



/*
Give Lots applicable Lot Status
*/



update ic_loct_inv_tmp l
set    lot_status =
       (select lot_status
       from    ic_loct_inv_sts s
       where   s.item_id = l.item_id
       and     s.whse_code = l.whse_code
       and     s.location = l.location
       and     s.lot_id = l.lot_id)
where exists
      (select NULL
      from    ic_loct_inv_sts s
      where   s.item_id = l.item_id
      and     s.whse_code = l.whse_code
      and     s.location = l.location
      and     s.lot_id = l.lot_id)
;



/*
Give Lots applicable QC Hold Reason Code
*/



update ic_loct_inv_tmp l
set    qchold_res_code =
       (select qchold_res_code
       from    ic_lots_sts s
       where   s.lot_status = l.lot_status)
where exists
(select NULL
from    ic_lots_sts s2
where   s2.lot_status = l.lot_status)
;



/*
Select records
- where there's a difference between old figures and new ones
- missing in old situation and present after re-calculation
- present in old situation and missing after re-calculation
*/



     column originates format a20 heading "Rebuild Result"



set termout on



select  'EXISTING RECORD' originates
,       i.item_no
,       l.whse_code
,       m.lot_no
,       m.sublot_no
,       l.location
,       l.loct_onhand
,      '1' sort_1
from    ic_item_mst i
,       ic_lots_mst m
,       ic_loct_inv l
,       ic_loct_inv_tmp t
where   l.item_id = t.item_id
and     l.whse_code = t.whse_code
and     l.lot_id = t.lot_id
and     l.location = t.location
and     l.loct_onhand <> t.loct_onhand
and     i.item_id = t.item_id
and     m.lot_id(+) = t.lot_id
UNION
select  'RECALCULATED RECORD '
,       i.item_no
,       t.whse_code
,       m.lot_no
,       m.sublot_no
,       t.location
,       t.loct_onhand
,      '2' sort_1
from    ic_item_mst i
,       ic_lots_mst m
,       ic_loct_inv l
,       ic_loct_inv_tmp t
where   l.item_id = t.item_id
and     l.whse_code = t.whse_code
and     l.lot_id = t.lot_id
and     l.location = t.location
and     l.loct_onhand <> t.loct_onhand
and     i.item_id = t.item_id
and     m.lot_id(+) = t.lot_id
UNION
select 'RECORD TO BE PURGED'
,      i.item_no
,      l.whse_code
,      m.lot_no
,      m.sublot_no
,      l.location
,      l.loct_onhand
,      '3' sort_1
from   ic_item_mst i
,      ic_lots_mst m
,      ic_loct_inv l
where  l.item_id &which_item_id
and    not exists
       (select NULL
       from    ic_loct_inv_tmp t
       where   t.item_id = l.item_id
       and     t.whse_code = l.whse_code
       and     t.lot_id = l.lot_id
       and     t.location = l.location)
and    i.item_id = l.item_id
and    m.lot_id(+) = l.lot_id
UNION
select 'RECORD TO BE ADDED '
,      i.item_no
,      t.whse_code
,      m.lot_no
,      m.sublot_no
,      t.location
,      t.loct_onhand
,      '4' sort_1
from   ic_item_mst i
,      ic_lots_mst m
,      ic_loct_inv_tmp t
where  not exists
       (select NULL
       from    ic_loct_inv l
       where   l.item_id = t.item_id
       and     l.whse_code = t.whse_code
       and     l.lot_id = t.lot_id
       and     l.location = t.location)
and    i.item_id = t.item_id
and    m.lot_id(+) = t.lot_id
order by 2
,        3
,        4
,        5
,       sort_1;



prompt
prompt
prompt If differences are displayed above,
accept update_yn prompt 'should these be corrected automatically (Y/N): '
prompt
set termout     off



delete from ic_loct_inv l
where  l.item_id &which_item_id
and    upper('&update_yn') = 'Y';



insert into ic_loct_inv
select item_id
,      whse_code
,      lot_id
,      location
,      loct_onhand
,      loct_onhand2
,      lot_status
,      qchold_res_code
,      0 -- delete_mark
,      NULL -- text_code
,      0 -- last_updated_by
,      0 -- created_by
,      sysdate -- last_update_date
,      sysdate -- creation_date
,      NULL -- last_update_login
,      NULL -- program_application_id
,      NULL -- program_id
,      NULL -- program_update_date
,      NULL -- request_id
from   ic_loct_inv_tmp
where  upper('&update_yn') = 'Y';



drop table ic_loct_inv_sts;



drop table ic_loct_inv_tmp;



spool off



exit