12 Replies Latest reply: Aug 25, 2014 12:44 PM by salute-Salem

# Calculating Units

hi all ,

i have a sales application for a pharmacy ,

```Create table stock (medicine varchar2(90) ,
max_Unit number ,
mid_unit numbe r,
min_unit number ,
max_to_min_factor number ,
mid_to_min_factor number ) ;

```
```insert into stock values ('cetal',1,2,3,20,20) ;

```

each medicine in the stock have these attributes :

max_unit -- for example "carton"

, mid_unit  -- "package"

, min_unit -- "tab"  ,

max_to_min_factor    -- the number of minimum units in the max unit ( the number of tabs in one carton ) .

mid_to_min_factor    -- the number of minimum units in the mid unit ( number of tabs in one package) .

for example , i have these attributes for a specific medicine respectively :

1- carton .

2- package .

3- tab .

4- 20 .

5- 2 .

- if the user buys two cartons or two packages , i convert it to the small unit and pass it to the stock (store) ,

the question is ,

- i want to calculate the "max" , "mid" , "min" units which resides in the stock which pertains to the specific medicine when there is

"5" tabs in the store for that medicine  -- remember  quantities are converted to the small unit before transferring them to the store .

- i think the question has nothing to do with oracle forms or the ide i work with , because finally i will calculate it with pl/sql .

thank you so much

.

• ###### 2. Re: Calculating Units

Maybe

with stock as

(select 'cetal' medicine,1 max_unit,2 mid_unit,3 min_unit,400 max_to_min_factor,20 mid_to_min_factor from dual union all

select 'latec',1,2,3,20,2 from dual

)

MEDICINEMAX_UNITMID_UNITMIN_UNITMAX_TO_MIN_FACTORMID_TO_MIN_FACTOR
cetal12340020

select :items min_units,

:medicine medicine,

trunc(:items / max_to_min_factor) max_units,

trunc(mod(:items,max_to_min_factor) / mid_to_min_factor) mid_units,

mod(:items,max_to_min_factor  / mid_to_min_factor) min_units

from stock

where medicine = :medicine

MIN_UNITSMEDICINEMAX_UNITSMID_UNITSMIN_UNITS
999cetal2919

Regards

Etbin

• ###### 3. Re: Calculating Units

i think you did not get me ,

i want to know the quantity of the medicine in the stock ,

in this case it would be :

0 carton.

2 packages .

1 tab .

because the package has 2 tabs .

of course there other medicines with different attributes , may be it could be

2 carton .

4 packages .

1 tab .

• ###### 4. Re: Calculating Units

Hi,

salute-Salem wrote:

i think you did not get me ,

i want to know the quantity of the medicine in the stock ,

in this case it would be :

0 carton.

2 packages .

1 tab .

because the package has 2 tabs .

of course there other medicines with different attributes , may be it could be

2 carton .

4 packages .

1 tab .

Sorry, I don't understand how you get the desired results from the given sample data.  Explain, step by step, how you figure those are the right results from that data.

It would help if you posted maybe 5 or 10 rows of sample data instead of just 1, along with the results you want from each.

• ###### 5. Re: Re: Calculating Units

Just changing data, column order and column names

with stock as

(select 'cetal' medicine,1 max_unit,2 mid_unit,3 min_unit,400 max_to_min_factor,20 mid_to_min_factor from dual

)

select :medicine medicine,

:items tabs_quantity,

trunc(:items / max_to_min_factor) cartons,

trunc(mod(:items,max_to_min_factor) / mid_to_min_factor) packages,

mod(:items,max_to_min_factor  / mid_to_min_factor) tabs

from stock

where medicine = :medicine

MEDICINETABS_QUANTITYCARTONSPACKAGESTABS
cetal9992919

Regards

Etbin

• ###### 6. Re: Calculating Units

the situation is :

- i have "5" tabs in the stock .

- the package contains "2" tabs , so the "5" tabs in the stock is "2" packages and "1" tab .

- so , i have "0" cartons , "2" packages , and "1" tab in the stock .                              -- i think it 's clear .

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

there is the situation from the beginning :

insert into stock

(medicine_name , quantity ,  max_unit , mid_unit , min_unit , max_to_min_factor , mid_to_min_factor )

values

('cetal'   ,5 ,       1 , 2 , 3 , 20 , 2)  ;

in the stock , this "5" tabs , should be  ("0" cartons , 2 packages , 1 tab) .

-------------------------------------------------------

insert into stock

(medicine_name , quantity ,  max_unit , mid_unit , min_unit , max_to_min_factor , mid_to_min_factor )

values

('buspar'   ,7 ,       1 , 2 , 3 , 20 , 2)  ;

-- 0 cartons ,  3 packages , 1 tab .

-------------------------------------------------------

insert into stock

(medicine_name , quantity ,  max_unit , mid_unit , min_unit , max_to_min_factor , mid_to_min_factor )

values

('gramycin'   ,20,       1 , 2 , 3 , 20 , 2)  ;

-- 1 carton , 0 packages , 0 tabs .

------------------------------------------------------

is it clear now ?

• ###### 7. Re: Calculating Units

i intended to put the quantity as "7" , because when you divide "7 / 20" , the result is less than "1"  .  and there is no "mod" .

there could be numbers which i could divide on the max_to_min_factor , and the result will be more than "1" or less .

• ###### 8. Re: Re: Calculating Units

Sorry, I don't know where I was looking at (the copy-paste dwarf did it again )

with stock as

(select 'cetal' medicine,5 quantity,1 max_unit,2 mid_unit,3 min_unit,20 max_to_min_factor,2 mid_to_min_factor from dual union all

select 'buspar',7,1,2,3,20,2 from dual union all

select 'gramycin',20,1,2,3,20,2 from dual

)

select medicine,

quantity,

trunc(quantity / max_to_min_factor) cartons,

trunc(mod(quantity,max_to_min_factor) / mid_to_min_factor) packages,

mod(quantity,mid_to_min_factor) tabs

from stock

MEDICINEQUANTITYCARTONSPACKAGESTABS
cetal5021
buspar7031
gramycin20100

Regards

Etbin

• ###### 9. Re: Calculating Units

select

trunc( 5 / max_to_min_factor)                              cartons

,trunc( mod(5, max_to_min_factor) / mid_to_min_factor)   packages

,5 - trunc( mod(5, max_to_min_factor) / mid_to_min_factor)

* mid_to_min_factor   tab

from stock

where medicine = 'cetal'

CARTONSPACKAGESTAB
021
• ###### 10. Re: Re: Re: Calculating Units

Sorry Etbin

but , there is something wrong ,

i have an item which has a  "Quantity" of "20" tabs (min_unit) , and   " 10 as a max_to_min_factor " , and " 3 as a mid_to_min_factor "  ,

i've tried this query , as you wrote :

select trunc(quantity / 10) cartons,

trunc(mod(quantity,10) / V_MID_FACTOR) packages,

mod(quantity,3) tabs

from ITEM_DETAIL

WHERE item = 1 ;

and i got

cartons packages tabs

2               0          2

which is completely wrong , i have to get

2   0    0

what is wrong with the code ?

thank you

• ###### 11. Re: Re: Re: Re: Calculating Units

Sorry (maybe this time ...)

with stock as

(select 'cetal' medicine,5 quantity,1 max_unit,2 mid_unit,3 min_unit,20 max_to_min_factor,2 mid_to_min_factor from dual union all

select 'buspar',7,1,2,3,20,2 from dual union all

select 'gramycin',20,1,2,3,10,3 from dual

)

select medicine,

quantity,

trunc(quantity / max_to_min_factor) cartons,

trunc(mod(quantity,max_to_min_factor) / mid_to_min_factor) packages,

quantity -

max_to_min_factor * trunc(quantity / max_to_min_factor) -

mid_to_min_factor * trunc(mod(quantity,max_to_min_factor) / mid_to_min_factor) tabs

from stock

MEDICINEQUANTITYCARTONSPACKAGESTABS
cetal5021
buspar7031
gramycin20200

Regards

Etbin

• ###### 12. Re: Calculating Units

This is Etbin i know .

thank you so much