Increase quantity field when duplicate serial numbers entered

user_7027412 0
Viewed 108

I'm trying to create POS system using oracle apex like the one at supermarkets, my issue is when the user scan the same item twice I want the application to increase it's quantity by '1' not to duplicate the record in my database, so I have write the below code but it doesn't work the way I want and it keep duplicating the item record on database.

declare v_count number;

begin

select COUNT(*) co
into v_count
from POS_TRANS_DETAIL
where PTM_ID = :P21_MASTER_ID and serial_number = :P21_SERIAL_NUMBER ;

if v_count >= 1 then
    update POS_TRANS_DETAIL
    set QUANTITY = v_count +1
    where PTM_ID = :P21_MASTER_ID and serial_number = :P21_SERIAL_NUMBER ;
end if;
EXCEPTION 
WHEN no_data_found THEN 
    begin
    insert into POS_TRANS_DETAIL values (
    TRANS_DETAIL_ID.nextval,
    :P21_PTM_ID,
    :P21_SERIAL_NUMBER,
    :P21_COST_PRICE,
    :P21_QUANTITY,
);
end;

end;

The first statement is for getting any duplicate serial number in the same receipt number (PTM_ID) and to store number of duplicate record on v_count, using the if statement I'm making sure that v_count has a value and then applying the update for quantity column, and in the exception section I'm handling new item insertion when no row return from the select statement which mean that the item didn't added before for the same receipt

I'm placing this code on a dynamic action fired when the user add new item.

oracle-apex plsql oracle
2 Answers

Rather than a drawn out procedure you just need a single statement: MERGE. What you are attempting is the very reason for it existence. I believe it needs to be rapped in a procedure (block) in Apex (but I do not actually know Apex.)

begin 
merge into pos_trans_detail ptd
    using (select :p21_master_id  ptm_id
                , :p21_serial_number serial_number
                , :p21_cost_price   cost_price
                , :p21_quantity    quantity
             from dual
          ) parm
    on (    ptd.ptm_id = parm. ptm_id
        and ptd.serial_number = parm.serial_number
       ) 
 when found then 
      update set quantity = quantity + parm.quantity
 when not found 
      insert into pos_trans_detail ptd(id, ptm_id,serial_number,cost_price ,quantity 
      values ( trans_detail_id.nextval 
            , parm.ptm_id
            , parm.serial_number
            , parm.cost_price
            , parm.quantity
            ); 
end ;
user_76238560

You have a couple of issues in your code. See below my coments

My version would look like this:

Update ( With debug )

declare 
v_count number;
begin
 
apex_debug.enable ( p_level => 5 );

select COUNT(*) into v_count
from POS_TRANS_DETAIL
where PTM_ID = :P21_MASTER_ID and serial_number = :P21_SERIAL_NUMBER ;

apex_debug.message(p_message => 'Counter is '||v_count||'', p_level => 5) ;

if v_count >= 1 then
    update POS_TRANS_DETAIL
    set QUANTITY = v_count +1
    where PTM_ID = :P21_MASTER_ID and serial_number = :P21_SERIAL_NUMBER ;
else
    insert into POS_TRANS_DETAIL values (
    TRANS_DETAIL_ID.nextval,
    :P21_PTM_ID,
    :P21_SERIAL_NUMBER,
    :P21_COST_PRICE,
    :P21_QUANTITY,
);
end if;

commit -- Apex does not autocommit, it depends on the method this code is assigned to ( submit page, after submit, etc ). Better use commit always

exception when others then raise;
end;

Key points

  • You are using exception when no data found to make an insert, but your code is doing select count, which is always found ( either 0 or some value ). So that part will never execute
  • I guess you wanted to insert when there is 0 in the counter, therefore it goes into the else section of that if-else statement.
  • Use commit if you are not sure your method does. Oracle Apex does autocommit if the method associated to this code is intended for it. For example, submit page. A good advice is always treat PLSQL code in Apex as if you would use it in Oracle DB directly. But use your common sense to use or not commit within your code in case you code is part of a compose process treated as a single transaction. Thanks to @Koen Lostrie for that remark!
user_137555380
Related