-- Listing 7-8. PL/SQL Code to Handle Shared Commissions
declare
    v_saleCommission number(7,2);
    v_amount         int := :P18_SALE_AMOUNT_SHARED;
    v_empCount       int;
    v_commissionEach number(7,2);
    v_empInfo        varchar2(25); 
    v_output         varchar2(250) := '';
begin
    -- Step 1: Calculate the new commission as 15% of the first $100 
    -- and then 10% of the remaining sale price.
    if v_amount > 100 then
          v_amount := v_amount - 100;
          v_saleCommission := 15 + (v_amount * 0.1);
    else
          v_saleCommission := v_amount * 0.15;
    end if;
 
    -- Step 2: Determine the shared commission.
    v_empcount := 1 + length(:P18_EMPNO_SHARED) 
                    - length(replace(:P18_EMPNO_SHARED, ':', '' ));
    v_commissionEach := v_saleCommission / v_empcount;

    -- Step 3: Update the EMP table.
    update EMP
    set Comm = Comm + v_commissionEach
    where instr( ':'||:P18_EMPNO_SHARED||':',  ':'||EmpNo||':' ) > 0;

    -- Step 4: Use a loop to collect employee info.
    for row in (
        select EName, Comm 
        from EMP 
        where instr( ':'||:P18_EMPNO_SHARED||':',  ':'||EmpNo||':' ) > 0 )
    loop
          v_empInfo := row.EName || ': ' || row.Comm || chr(13);
          v_output := v_output || v_empInfo;
    end loop;

    -- Step 5: Write the item values.
    :P18_GROSS_COMMISSION := v_saleCommission;
    :P18_COMMISSION_EACH  := v_commissionEach;
    :P18_NEW_COMMISSIONS  := v_output;
end;

