Redistribute Records Dynamically

[HezMac]

Hi there.

I'm using a PL/SQL back end (and VB.Net front end) to develop an application that will take accounts from one employee (the Source) and reassign them to one or more than one other employees (the Destination).

It's when the accounts are reassigned to more than one employee that I'm having issues with.

For example, if one employee leaves the company and her accounts are being reassigned to four other employees, I want to have the accounts distributed by account balance, so they will be distributed somewhat equally.

For instance, if the Source account balances are: $1000, $850, $700, $600, $500, $300, $250 they would be distributed between the four employees as follows:


Employee 1st Assigned Acct 2nd Assigned Acct
Joe $1000 -
Samantha $850 $250
Erin $700 $300
Kate $600 $500


The user selects the Destination employees from a drop down list on the VB form (and adds them to a list box), so I need a way to count the employess in the list box and distribute the accounts in a descending, and then ascending way, once at the bottom of the list of destination employees.

Any ideas of where to start?

Hope this makes sense.

Thanks a bunch for any help.

 

[lava1178]

I am not sure of how it works in VB but for Oracle forms as fron end -if you have to get the no. of elements present in the List box, You have an option of
GET_LIST_ELEMENT_COUNT(list_id);
Where you first find what is the Id corresponding to the list item defined thru' (ithink ..its)FIND_ITEM_ID(<list name>);

 

[hilfy]

Not code, but the basic process you'll do for assigning the accounts:

Open a cursor with the account information, ordered by account balance descending.
n := the number of employees in the list
i := 0  (assuming a 0-based list...)
forward := true
While not cursor.EOF 
  assign account to employee[i]
  if forward then
    if i = n then
      forward := false
    else
      increment i
    end if
  else
    if i = 0 then
      forward := true
    else
      decrement i
    end if
  end if
  cursor.next

Translate this into whatever programming language you want - VB.Net or PL/SQL.

-Dell