RE: View question

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Wed, 06 Aug 2003 17:19:23 -0800
Message-ID: <F001.005C94EC.20030806171923@fatcity.com>

I got the first part of the query, sum of entrace an exit per item, and the initial inventary that I will use has stock on hand in case we don't have transactions made on the initial date on the rango or later.

SELECT ICTRANS.COMPANY, ICTRANS.LOCATION, ICTRANS.ITEM, ITEMMAST.INVEN_MAJCL, ITEMMAST.INVEN_MINCL, ITEMLOC.SOH_QTY, SUM( CASE WHEN ICTRANS.QUANTITY>0 THEN ICTRANS.QUANTITY ELSE 0 END) ENTRADA, SUM( CASE WHEN ICTRANS.QUANTITY<0 THEN ICTRANS.QUANTITY ELSE 0 END) SALIDA FROM ICTRANS INNER JOIN

      ITEMMAST ON ICTRANS.ITEM = ITEMMAST.ITEM INNER JOIN
      ITEMLOC ON ICTRANS.COMPANY = ITEMLOC.COMPANY AND ICTRANS.LOCATION = ITEMLOC.LOCATION AND 
      ICTRANS.ITEM = ITEMLOC.ITEM

WHERE (ICTRANS.COMPANY = 2000) AND (ICTRANS.LOCATION = 'TJU01') AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('08/01/2003','MM/DD/YYYY') AND TO_DATE('08/06/2003','MM/DD/YYYY') and ITEMMAST.INVEN_MAJCL='110'
GROUP BY ICTRANS.COMPANY, ICTRANS.LOCATION, ICTRANS.ITEM, ITEMMAST.INVEN_MAJCL, ITEMMAST.INVEN_MINCL,ITEMLOC.SOH_QTY Here I am using a ITEMMAST ( the master table of items) too because they want to filter information per Inventory clases (ITEMMAST.INVEN_MAJCL).

I just want to find the way of join it why:

SELECT DISTINCT ITEM, SOH_QTY, UPDATE_DATE, UPDATE_TIME FROM ICTRANS b1
WHERE
(COMPANY = 2000) AND (LOCATION = 'TJU01')
and

    (UPDATE_DATE=

                 (SELECT     Max(UPDATE_DATE)AS FECH_HORA
                  FROM          ICTRANS
                  WHERE      UPDATE_DATE <= to_date('08/01/2003', 'mm/dd/yyyy') AND (COMPANY = b1.COMPANY) AND (LOCATION = b1.LOCATION) AND (ITEM = b1.ITEM)
                   GROUP BY ITEM))

AND
(UPDATE_TIME=

                          (SELECT     MIN(UPDATE_TIME)AS HORA
                            FROM          ICTRANS
                            WHERE      (UPDATE_DATE = b1.UPDATE_DATE) AND (COMPANY = b1.COMPANY)AND (LOCATION = b1.LOCATION) AND (ITEM = b1.ITEM)
                            GROUP BY  ITEM))

ORDER by ITEM

>>> Teresita.Castro_at_s-martmx.com 08/06/03 06:24PM >>>

Thanks for answer me.

The problem that I have is the next, I have to calculate from a item transaction table the stock on hand to determinate date, (to made different reports).

To do this I made the next query:

SELECT DISTINCT ITEM, SOH_QTY, UPDATE_DATE, UPDATE_TIME FROM ICTRANS b1
WHERE
(COMPANY = 2000) AND (LOCATION = 'TJU01')
and

    (UPDATE_DATE=

                 (SELECT     Max(UPDATE_DATE)AS FECH_HORA
                  FROM          ICTRANS
                  WHERE      UPDATE_DATE <= to_date('08/01/2003', 'mm/dd/yyyy') AND (COMPANY = b1.COMPANY) AND (LOCATION = b1.LOCATION) AND (ITEM = b1.ITEM)
                   GROUP BY ITEM))

AND
(UPDATE_TIME=

                          (SELECT     MIN(UPDATE_TIME)AS HORA
                            FROM          ICTRANS
                            WHERE      (UPDATE_DATE = b1.UPDATE_DATE) AND (COMPANY = b1.COMPANY)AND (LOCATION = b1.LOCATION) AND (ITEM = b1.ITEM)
                            GROUP BY  ITEM))

ORDER by ITEM

Now I have to made a report that have the next information: Item, stock on hand , entraces and exits per item in determinate period of time. To determinate if is an entrace or exit depend of the sign of the Ictrans.quantity field.

In this example the 08/01/2003 date will be the initial date.

This is a new system and maybe we will not have movements on the initial date or after it, so I have to bring the initial inventory from another table, ITEMLOC ( item by location) that have the initial inventory of the item.

So the query will be something like :

select company,location, item, sum (case when quantity<0 then quantity else 0 end) exit, sum(case when quantity>=0 then quantity else 0 end) entrace
from ictrans
where company=2000 and location='TJU01' and update_date between to_date('08/01/2003','mm/dd/yyyy') and to_date('08/06/2003','mm/dd/yyyy') group by company,location, item

Union with the result of the item stock on hand (SOH_QTY) of the first query, or in case that this result is null, to obtain the stock on hand I have to consult the ITEMLOC table

Select item, soh_qty from itemloc where company=2000 and location='TJU01'

I hope I made my self clear and you undestand my explaination. What I want to do it a view(vw_stockonhand) to obtain the initial inventory then made a left join to the ictrans table, so in case that I don't have movements on the initial date or after it returns me a null, and another left join with the Itemloc, so if vw_stockonhand is null I get the itemloc.soh_qty.

Thanks for you time  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Teresita Castro
  INET: Teresita.Castro_at_s-martmx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Wed Aug 06 2003 - 20:19:23 CDT