PL/SQL Simple logic error, cant figure out

Question

Ok so this is my code....

      DECLARE
  V_INVENTORY_ITEM     INVENTORY.ITEM%TYPE;
  V_INVENTORY_PRICE    INVENTORY.PRICE%TYPE;
  V_INVENTORY_ONHAND   INVENTORY.ONHAND%TYPE;
  V_TRANS_ITEM          TRANSACTION.ITEM%TYPE;
  V_TRANS_CODE          TRANSACTION.CODE%TYPE;
  V_NEW_INVE_ITEM       NEW_INVENTORY.ITEM%TYPE;
  V_NEW_INVE_SOLD       NEW_INVENTORY.SOLD%TYPE;
  V_NEW_INVE_RETURNED   NEW_INVENTORY.RETURNED%TYPE;
  V_NEW_INVE_ONHAND     NEW_INVENTORY.ONHANDNEW%TYPE;
  V_NEW_INVE_PURCHASED  NEW_INVENTORY.PURCHASED%TYPE;
  V_NEW_INVE_ORIGINAL   NEW_INVENTORY.ONHANDORIG%TYPE;
CURSOR INVEN_CURSOR IS
     SELECT ITEM, PRICE, ONHAND FROM INVENTORY
     ORDER BY ITEM;
  CURSOR TRANS_CURSOR IS
     SELECT ITEM, CODE FROM TRANSACTION
     WHERE V_INVENTORY_ITEM = ITEM
     ORDER BY ITEM;
BEGIN
    OPEN INVEN_CURSOR;
    LOOP
    FETCH INVEN_CURSOR INTO V_INVENTORY_ITEM, V_INVENTORY_PRICE, V_INVENTORY_ONHAND;
    EXIT WHEN INVEN_CURSOR%NOTFOUND;
    V_NEW_INVE_SOLD := 0;
    V_NEW_INVE_RETURNED := 0;
    V_NEW_INVE_ONHAND := 0;
    V_NEW_INVE_PURCHASED := 0;
    V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
    OPEN TRANS_CURSOR;
    LOOP
    FETCH TRANS_CURSOR INTO V_TRANS_ITEM, V_TRANS_CODE;
    EXIT WHEN TRANS_CURSOR%NOTFOUND;

IF V_TRANS_CODE = 'P' THEN
V_NEW_INVE_ONHAND := V_INVENTORY_ONHAND + 1;
V_NEW_INVE_PURCHASED := V_NEW_INVE_PURCHASED + 1;
V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
END IF;
IF V_TRANS_CODE = 'R' THEN
V_NEW_INVE_RETURNED := V_NEW_INVE_RETURNED + 1;
V_NEW_INVE_ONHAND := V_INVENTORY_ONHAND + 1;
V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
END IF;
IF V_TRANS_CODE = 'S' THEN
V_NEW_INVE_SOLD := V_NEW_INVE_SOLD + 1;
V_NEW_INVE_ONHAND := V_INVENTORY_ONHAND - 1;       
V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
END IF;

END LOOP;
INSERT INTO NEW_INVENTORY 
VALUES(V_INVENTORY_ITEM, V_NEW_INVE_SOLD, V_NEW_INVE_PURCHASED, V_NEW_INVE_RETURNED, V_INVENTORY_ONHAND, V_NEW_INVE_ONHAND);
CLOSE TRANS_CURSOR;
END LOOP;
CLOSE INVEN_CURSOR;
END;
/

I'm trying to update a table, which is an inventory table... this reads the transaction table and updates a new table...(the new inventory) on my if statements something is wrong, because every variable comes out as 0; any suggestions?

my tables

SQL> select * from inventory;

ITEM                 PRICE     ONHAND
--------------- ---------- ----------
BALL                 12.99          5
PEN                   1.99         10
PENCIL                2.99          1
PAPER                 5.99          3
ERASER                 .99          6
BACKPACK             19.99         10
STAPLER               3.99         12
RULER                 4.99          9
NOTEBOOK              6.99         12

9 rows selected.

SQL>

SQL> select * from transaction;

ITEM            CO
--------------- --
BALL            P
BALL            R
BALL            S
BALL            S
BALL            S
PEN             R
PEN             S
PEN             S
PEN             P
PENCIL          S
PENCIL          R
PENCIL          S
PENCIL          P
PAPER           S
PAPER           S
PAPER           S
ERASER          R
ERASER          S
ERASER          S
ERASER          P
BACKPACK        S
BACKPACK        S
BACKPACK        S
BACKPACK        P
STAPLER         R
STAPLER         S
RULER           S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S

33 rows selected.

SQL>


SQL> select * from new_inventory;

ITEM                  SOLD   RETURNED     ONHAND
--------------- ---------- ---------- ----------
BACKPACK                 0          0          0
BALL                     0          0          0
ERASER                   0          0          0
NOTEBOOK                 0          0          0
PAPER                    0          0          0
PEN                      0          0          0
PENCIL                   0          0          0
RULER                    0          0          0
STAPLER                  0          0          0

9 rows selected.

Answer 1

Try putting the following statements before opening the TRAN_CURSOR

V_NEW_INVE_SOLD := 0;
V_NEW_INVE_RETURNED := 0;
V_NEW_INVE_ONHAND := 0;

Answer 2

There is no reason for needing to a) reinvent the join (you've got two cursors that you're manually doing a nested loop join on - why do that, when the Oracle Optimizer is perfectly capable of joining two tables together and deciding the best join method itself?) and b) doing the calculations and inserts row-by-row (aka slow-by-slow).

Instead, you can achieve the whole thing in a single insert statement like so:

insert into new_inventory (item,
                           new_inve_purchased,
                           new_inve_returned,
                           new_inve_sold,
                           orig_onhand,
                           new_inve_onhand) -- Amend as appropriate; I guessed at what the new_inventory column names were.
select item,
       nvl(new_inve_purchased, 0) new_inve_purchased,
       nvl(new_inve_returned, 0) new_inve_returned,
       nvl(new_inve_sold, 0) new_inve_sold,
       nvl(onhand, 0) orig_onhand,
       nvl(onhand, 0)
         + nvl(new_inve_purchased, 0) 
         + nvl(new_inve_returned, 0)
         - nvl(new_inve_sold, 0) new_inve_onhand
from   (select   inv.item,
                 inv.onhand,
                 trn.code
        from     inventory inv
                 inner join transaction trn on (inv.item = trn.item))
pivot (sum(1) for code in ('P' as new_inve_purchased,
                           'R' as new_inve_returned,
                           'S' as new_inve_sold));

The benefits of using a single SQL statement to do the work are:

1. It's easier to debug - you can run the select statement on its own to see what it's doing
2. It'll be more performant; you're letting the database SQL engine do the majority of the work, rather than having PL/SQL talk to SQL, and SQL returning results back to PL/SQL for each row in the inventory table.
3. Because it's much more compact than the corresponding PL/SQL, there's less code to keep track of, making it much easier to read and understand.

Note also that I've specified the list of columns that you're inserting into (although I had to guess at their names - you'll need to amend as appropriate!).

This is good practice (especially if it's code that will end up in production!) as failing to do so could lead to trouble down the line when someone adds a new column into the table. Best be specific now, and avoid such problems entirely!