/*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 L.lname AS [Locn],
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],
Q.qty AS [Qty],
Isnull(Q.qtyinrack, 0) AS [Qty in Rack],
CASE
WHEN m.product_type_v41 = '0' THEN
(SELECT Isnull(Sum(AT.qtycheckedout - AT.qtyreturned), '')
FROM tblassetran AT
LEFT OUTER JOIN tblbookings b ON b.booking_no = at.booking_no
WHERE AT.product_code = M.product_code
AND ( AT.stock_number = 65535 )AND ( B.from_locn = q.locn )
/*AND B.booking_type_v32 in (0, 4, 6, 14, 15, 16, 17)*/
)
END AS [OUT ON BOOKINGS]
FROM tbllocnqty Q
LEFT OUTER JOIN tblinvmas m ON m.product_code = q.product_code
LEFT OUTER JOIN tbllocnlist L ON L.locn_number = q.locn
WHERE ( m.asset_track = 'N' AND m.product_type_v41 IN ( 0 ) )
AND q.locn IN ( 0, 1, 2 )
ORDER BY q.locn, m.product_code
/* 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