SELECT RECEIPT_NUM,
APS.VENDOR_NAME,
RSL.LINE_NUM RECT_LN_NUMBER,
PLT.PURCHASE_BASIS LINE_TYPE,
MSI.SEGMENT1 ITEM_CODE,
MSI.DESCRIPTION,
PL.ATTRIBUTE1 COUNTRY_ORIGIN,
RCT.PRIMARY_UNIT_OF_MEASURE,
QUANTITY_RECEIVED,
NVL (RCT.QUANTITY, SOURCE_DOC_QUANTITY) RETURN_QTY,
PL.UNIT_PRICE,
PL.UNIT_PRICE * NVL (RCT.QUANTITY, SOURCE_DOC_QUANTITY),
QUANTITY_SHIPPED RTN_VALUE,
LOT.LOT_NUM BARCODE,
'SUPP_BATCH' SUPP_BATCH,
LOT.EXPIRATION_DATE EXP_DATE
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RCT,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_LINE_TYPES PLT,
RCV_LOT_TRANSACTIONS LOT,
MTL_SYSTEM_ITEMS_B MSI,
AP_SUPPLIERS APS
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND rct.transaction_type = 'RETURN TO VENDOR'
AND PL.PO_LINE_ID = RCT.PO_LINE_ID
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PLT.LINE_TYPE_ID = PL.LINE_TYPE_ID
AND lot.TRANSACTION_ID(+) = RCT.TRANSACTION_ID
AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
AND RCT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND APS.VENDOR_ID=RCT.VENDOR_ID
AND RECEIPT_NUM ='23684'
--BETWEEN :P__RCPT_FROM AND :P_RCPT_TO
--AND RCT.TRANSACTION_DATE BETWEEN :P_FROM AND :P_TO
