upd_cost.sql: cost update fix

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: sample program to fix cost problem on order information.
Code Listing:

/* program: /users/common/sql/update_cost.sql [upd_cost.sql]
   date   : 09/18/96
   author : Robert Lawson
   purpose: Fix bug in oelsc that missed cost updates for backorders
*/
--set termout on
-- set echo on
set heading off
set linesize 132
set feedback off
set pagesize 60
/*
===============================================================
Report
===============================================================
*/
spool /users/common/oracle/upd_cost
SELECT
od.order_no,
od.order_suffix,
od.line_no,
od.UNIT_COST wrong_cost,
od0.unit_cost corrt_cost
FROM oedetl od0, oedetl od
WHERE
od.order_suffix <> 0 and
od0.plant = od.plant and
od0.order_no = od.order_no and
od0.order_suffix = 0 and
od0.line_no = od.line_no and
nvl(od0.unit_cost,0) <> nvl(od.unit_cost,0)
ORDER BY 1,2,3;
spool off

/*
===============================================================
Update backorder items
===============================================================
*/
-- set serveroutput on

DECLARE

o_plant    oedetl.plant%TYPE;
o_order_no   oedetl.order_no%TYPE;
o_order_suffix   oedetl.order_suffix%TYPE;
o_line_no  oedetl.line_no%TYPE;
o_unit_cost   oedetl.unit_cost%TYPE;
o_UNIT_LABOR_COST oedetl.unit_labor_cost%TYPE;
o_UNIT_MATL_COST  oedetl.unit_matl_cost%TYPE;
o_UNIT_OVHD_COST oedetl.unit_ovhd_cost%TYPE;
o_rowid                 ROWID;

n_unit_cost   oedetl.unit_cost%TYPE;
n_UNIT_LABOR_COST oedetl.unit_labor_cost%TYPE;
n_UNIT_MATL_COST  oedetl.unit_matl_cost%TYPE;
n_UNIT_OVHD_COST oedetl.unit_ovhd_cost%TYPE;

l_count           numeric(10,5) := 0; /* commit counter */

cursor oedetl_n is
SELECT
od.plant,
od.order_no,
od.order_suffix,
od.line_no,
nvl(od.UNIT_COST,0),
nvl(od.UNIT_LABOR_COST,0),
nvl(od.UNIT_MATL_COST,0),
nvl(od.UNIT_OVHD_COST,0),
rowid
FROM oedetl od
WHERE
od.order_suffix <> 0
ORDER BY 1,2,3;

cursor oedetl_0 is
SELECT
nvl(od.UNIT_COST,0),
nvl(od.UNIT_LABOR_COST,0),
nvl(od.UNIT_MATL_COST,0),
nvl(od.UNIT_OVHD_COST,0)
FROM oedetl od
WHERE
od.plant = o_plant and
od.order_no = o_order_no and
od.order_suffix = 0 and
od.line_no = o_line_no;

/*
===============================================================
Get backorder lines
===============================================================
*/
BEGIN
open oedetl_n;
loop
fetch oedetl_n into
o_plant, o_order_no, o_order_suffix, o_line_no, o_UNIT_COST, o_UNIT_LABOR_COST,
o_UNIT_MATL_COST, o_UNIT_OVHD_COST, o_rowid;
exit when oedetl_n%notfound;

open oedetl_0;
loop
fetch oedetl_0 into
n_UNIT_COST, n_UNIT_LABOR_COST, n_UNIT_MATL_COST, n_UNIT_OVHD_COST;
exit when oedetl_0%notfound;

if (n_unit_cost <> o_unit_cost or n_UNIT_LABOR_COST <> o_UNIT_LABOR_COST or
n_UNIT_MATL_COST <> o_UNIT_MATL_COST or n_UNIT_OVHD_COST <> o_UNIT_OVHD_COST)
then
  dbms_output.put_line(o_order_no' 'o_order_suffix' ' o_line_no' o: 'o_unit_cost' n: 'n_unit_cost);
  UPDATE oedetl SET
  UNIT_COST = n_unit_cost, UNIT_LABOR_COST = n_UNIT_LABOR_COST,
  UNIT_MATL_COST = n_UNIT_MATL_COST, UNIT_OVHD_COST = n_UNIT_OVHD_COST
  WHERE rowid = o_rowid;

  l_count := l_count +1;
  if mod(l_count,20) = 0 then
    commit;
    l_count := 0;
  end if;
end if;
end loop; /* oedetl_0 */
close oedetl_0;

end loop; /* oedetl_n */
close oedetl_n;
commit;

END;
/
exit