SAMya project

Simulation

Simulation models imitate real life systems. The models frequently deal with stochastic rather than deterministic inputs. These inputs have probabilistic components that can be modeled through the use of random numbers. Simulation languages and spreadsheets have built-in probability functions that allow the user to generate values from certain probability distributions.

I had mad a simulation model by excel which Simulate four cycles done in main inventories:

1- Determination of next year drugs quantity. [ For tender ]

2- Choosing Company from tender. [ For best offer ]

3- Asking and adding supply orders from companies. [ Check for safety ]

4- Ordering from pharmacies. [ Check for safety ]

 

Note: this model assumes using one drug.

** Determination of next year drugs quantity.

First the initializations of this year usage by generating random monthly usage ranged by minimum and maximum usage, then using forecasting to forecast the next year monthly usage, now it is so easy to calculate the average usage (M).

The wanted quantity determined by the next formula:

Quantity = (1+ incremental ratio + safety ratio) * mean usage (M)

** Choosing Company from tender.

Now we have a wanted drug quantity, which means making tender for this quantity. Tender must provide information about entered companies, this information assumed in this model as follow;

1- Unite Price

2- Confidence

3- Quality

Note: you can add more parameters to the model.

For Unite price we must convert it into weight. Then add the weight to confidence and quality.

Total weight = (price (weight) + Confidence + Quality)

Now it is easy to choose the company for its offer by choosing greatest total weight .

** Requesting and adding supply orders

from companies.

This part is a day-to-day simulation, in which inventory checks it stock for reorder point, then ask for supply order if no safety found.

This part is important to check for shortage in every day in year, if shortage found in any day this means that we must change the only parameter available for inventories, it's the reorder point.

Not: shortage is a catastrophe in this case.

** Ordering from pharmacies.

Like the relation between inventories and company.

Note: we assume one pharmacy represent all pharmacies. also this model has high flexibility.

If you want to see the model connect me.

*************************************************************************************************************

Oracle simulation

In my project I wrote over 1000 row as plan text to insert the test data (for 20 record only), but here you can insert over one million record in one second saving time and effort.

Populating item table

First create test table named (itemo) from table item [to make table item safe from any unwanted actions]

Create table itemo as select * from item;

Now we must empty itemo table [to add our randomly generated records]

Delete from tembo;

Now let my populating script work,

Begin

For id in 1..100 loop

Insert into itemo values

(id,

floor(dbms_random.value(1,20)),

dbms_random.string('u',dbms_random.value(5,10)),

decode(floor(dbms_random.value(1,5)),

1,'drops',

2,'powder',

3,'ampol',

4,'drink',

5,'capsules',

'x'),

1,

floor(dbms_random.value(300,1000)),

floor(dbms_random.value(20000,50000)),

 decode(floor(dbms_random.value(1,7)),

1,'MIDUM',

2,'LOW',

3,'HIGH',

4,'NO',' '),

dbms_random.string('l',dbms_random.value(3,7))||' '||dbms_random.string('l',dbms_random.value(3,7))||' '||dbms_random.string('l',dbms_random.value(3,7)));

End loop;

Commit;  

End;

Now we see 100 valed records in table itemo

In the end we can say that we have a small example for using simulation in oracle, as we can build a simulation model for our entire database to see its actions and errors, helping in avoiding bugs and problems in future.