12 Replies Latest reply: Aug 25, 2014 7:44 AM by salute-Salem RSS

    Calculating Units

    salute-Salem

      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

      .

        • 1. Re: Calculating Units
          Rahul_India
          • 2. Re: Calculating Units
            Etbin

            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
              salute-Salem

              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
                Frank Kulash

                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
                  Etbin

                  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
                    salute-Salem

                    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
                      salute-Salem

                      in reply to etbin ,

                       

                      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
                        Etbin

                        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
                          chris227

                          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
                            salute-Salem

                            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
                              Etbin

                              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
                                salute-Salem

                                This is Etbin i know .

                                 

                                thank you so much