/*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