Stock Take - Non Barcoded Items

/*All NON Barcode tracked Rental Items in Inventory Master with a count of whats out on a booking */

Select m.product_code AS [PRODUCT CODE], 

         m.descriptionV6 AS [DESCRIPTION], 

        ('$' + convert(VARCHAR,convert(money,m.cost_price),1)) as [COST PRICE], 

        m.on_hand as [TOTAL INVENTORY],

CASE When m.product_type_v41 = '0' Then

    (SELECT SUM(AT.qtycheckedOut)

    FROM tblAssetran AT

    WHERE AT.product_code = M.PRODUCT_COde

    AND (AT.stock_number = 65535 ) and (AT.qtycheckedout > at.Qtyreturned)

    /*AND B.booking_type_v32 in (0, 4, 6, 14, 15, 16, 17)*/

    )

       END [OUT ON BOOKINGS]

from tblInvmas M

Where m.asset_track = 'N' and m.product_type_v41 in (0)

Order by m.product_code

/* Non Barcode Tracked Gear by Location 0,1,2 Please contact support@rentp.com to add more locations*/

SELECT m.product_code                                          AS [PRODUCT CODE]
       ,
       m.descriptionv6                                         AS
       [DESCRIPTION],
       ( '$'
         + CONVERT(VARCHAR, CONVERT(MONEY, m.cost_price), 1) ) AS [COST PRICE],
       m.on_hand                                               AS
       [TOTAL INVENTORY],
       (SELECT L.qty
        FROM   tbllocnqty L
        WHERE  m.product_code = L.product_code
               AND L.locn = 0)                                 AS 'Locn 0',
       (SELECT L.qty
        FROM   tbllocnqty L
        WHERE  m.product_code = L.product_code
               AND L.locn = 1)                                 AS 'Locn 1',
       (SELECT L.qty
        FROM   tbllocnqty L
        WHERE  m.product_code = L.product_code
               AND L.locn = 2)                                 AS 'Locn 2',
       CASE
         WHEN m.product_type_v41 = '0' THEN (SELECT Sum(AT.qtycheckedout)
                                             FROM   tblassetran AT
                                             WHERE
         AT.product_code = M.product_code
         AND ( AT.stock_number = 65535 )
         AND ( AT.qtycheckedout >
               at.qtyreturned )
                                            /*AND B.booking_type_v32 in (0, 4, 6, 14, 15, 16, 17)*/
                                            )
       END                                                     [OUT ON BOOKINGS]
FROM   tblinvmas M
WHERE  m.asset_track = 'N'
       AND m.product_type_v41 IN ( 0 )
ORDER  BY m.product_code  

/* All NON Barcode Tracked that are out on a booking */

SELECT     AT.product_code [Product Code], 

               I.descriptionV6 [Product Description], 

              AT.booking_no [Out on booking], 

              AT.qtyCheckedout, 

             At.qtyreturned, 

             AT.ActOutDate, 

             AT.ActInDate

FROM tblassetran AT left outer join tblInvmas I on AT.product_code = I.product_code

WHERE  AT.stock_number = 65535 and (AT.qtycheckedout > at.Qtyreturned)

ORDER BY AT.product_code,  AT.booking_no


/* All SALES Stock */

Select m.product_code, m.descriptionV6, m.on_hand

from tblInvmas M

Where m.product_type_v41 in (3)

Order by m.product_code