Change the booking number to the one you are working on.
This query will show all sub rentals, transfers, PO's assigned to your booking
DECLARE @MYBOOKING VARCHAR(13) = 'CATHAY00003'
DECLARE @Items TABLE
(
[id] DECIMAL(10, 0),
firstdate DATETIME,
retndate DATETIME,
booking_no VARCHAR(13),
price FLOAT,
from_locn INT,
trans_to_locn INT,
product_code_v42 VARCHAR(8),
trans_type_v41 TINYINT,
trans_qty INT,
itemdesc VARCHAR(50),
showstarttime VARCHAR(4),
actualpocurrency VARCHAR(5),
pponumber DECIMAL(19, 0)
)
INSERT INTO @Items
([id],
firstdate,
retndate,
price,
from_locn,
trans_to_locn,
product_code_v42,
trans_type_v41,
booking_no,
trans_qty,
itemdesc,
showstarttime)
SELECT I.id,
firstdate,
retndate,
price,
B.from_locn,
B.trans_to_locn,
product_code_v42,
trans_type_v41,
booking_no_v32,
trans_qty,
comment_desc_v42,
B.showstarttime
FROM dbo.tblitemtran I
INNER JOIN dbo.tblbookings B
ON I.booking_no_v32 = B.booking_no COLLATE database_default
WHERE B.assigned_to_v61 = @MYBOOKING
INSERT INTO @Items
([id],
trans_type_v41,
itemdesc,
booking_no,
trans_qty,
price,
from_locn,
trans_to_locn,
showstarttime)
SELECT I.id,
13,
sundry_desc,
booking_no_v32,
trans_qty,
sundry_price,
B.from_locn,
B.trans_to_locn,
B.showstarttime
FROM dbo.tblsundry I
INNER JOIN dbo.tblbookings B
ON I.booking_no_v32 = B.booking_no COLLATE database_default
WHERE B.assigned_to_v61 = @MYBOOKING
INSERT INTO @Items
([id],
firstdate,
retndate,
product_code_v42,
trans_type_v41,
booking_no,
trans_qty,
itemdesc,
price,
from_locn,
trans_to_locn,
showstarttime)
SELECT I.id,
firstdate,
retndate,
I.product_code_v42,
booking_type_v32,
booking_no_v32,
I.trans_qty,
descriptionv6,
techpay,
B.from_locn,
B.trans_to_locn,
B.showstarttime
FROM dbo.tblcrew I
INNER JOIN tblinvmas M
ON I.product_code_v42 = M.product_code COLLATE database_default
INNER JOIN dbo.tblbookings B
ON I.booking_no_v32 = B.booking_no COLLATE database_default
WHERE B.assigned_to_v61 = @MYBOOKING
UPDATE @Items
SET actualpocurrency = (SELECT TOP 1 P.actualpocurrency
FROM tblpo P
WHERE P.pobooking_no = booking_no)
UPDATE @Items
SET pponumber = (SELECT TOP 1 P.pponumber
FROM tblpo P
WHERE P.pobooking_no = booking_no)
SELECT B.id,
B.firstdate,
B.retndate,
B.booking_no,
B.price,
B.from_locn,
B.trans_to_locn,
B.product_code_v42,
B.trans_type_v41,
B.trans_qty,
B.itemdesc,
B.showstarttime,
B.actualpocurrency,
b.pponumber,
(SELECT TOP 1 organisationv6
FROM tblcust
WHERE customer_code = Substring(B.booking_no, 1, Len(B.booking_no) - 5)
COLLATE
database_default) AS OrganisationV6,
(SELECT TOP 1 vendorname
FROM tblvendor
WHERE vendorcode = Substring(B.booking_no, 1, Len(B.booking_no) - 5)
COLLATE
database_default) AS VendorName,
(SELECT TOP 1 currencystr
FROM tblcust
WHERE customer_code = Substring(B.booking_no, 1, Len(B.booking_no) - 5)
COLLATE
database_default) AS CustCurrency,
(SELECT TOP 1 vcurrency
FROM tblvendor
WHERE vendorcode = Substring(B.booking_no, 1, Len(B.booking_no) - 5)
COLLATE
database_default) AS VendCurrency
FROM @Items B
WHERE Len(B.booking_no) > 5
ORDER BY B.booking_no
SELECT A.product_code,
I.descriptionv6 AS ProductDescription,
B.booking_no,
B.organizationv6,
A.qtycheckedout,
A.actoutdate,
A.actindate,
(SELECT TOP 1 P.pponumber
FROM tblpo P
WHERE P.pobooking_no = b.booking_no) AS PPONumber
FROM tblbookings B
INNER JOIN dbo.tblassetran A
ON B.booking_no = A.booking_no
LEFT OUTER JOIN dbo.tblinvmas I
ON A.product_code = I.product_code
WHERE assigned_to_v61 = @MYBOOKING
AND booking_type_v32 = 22
SELECT P.pponumber,
P.expecteddate,
P.actualpocurrency,
L.lproductcode,
L.lquantity,
L.lprice,
L.lfftext,
V.vendorname,
V.vcurrency,
Isnull(B.currencystr, '') AS CurrencyStr
FROM tblpo P
INNER JOIN tblpoline L
ON L.ponumber = P.pponumber
INNER JOIN tblvendor V
ON V.vendorcode = P.pvendorcode
LEFT OUTER JOIN tblbookings B
ON B.booking_no = P.pobooking_no
WHERE P.pobooking_no = @MYBOOKING
AND P.crossrental <> 'Y'