Quantity pending against receipt Query
SELECT RSH.RECEIPT_NUM RECEIPT_NUM, RSH.CREATION_DATE CREATION_DATE, RT.TRANSACTION_TYPE TRANSACTION_TYPE, NVL(RT.QUANTITY, 0) QUANTITY,rt.destination_type_code destination_type_code,
RT.UNIT_OF_MEASURE UNIT_OF_MEASURE, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION, NVL(RT.PO_UNIT_PRICE, 0) PO_UNIT_PRICE,
RT.CURRENCY_CODE CURRENCY_CODE, NVL(RT.CURRENCY_CONVERSION_RATE,0) RATE, RSL.SHIPMENT_LINE_ID SHIPMENT_LINE_ID,
NVL(RT.QUANTITY * RT.PO_UNIT_PRICE * RT.CURRENCY_CONVERSION_RATE ,0) VALUE
FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_VENDORS POV, PO_VENDOR_SITES POVS,
ORG_ORGANIZATION_DEFINITIONS ORG, HR_LOCATIONS_ALL_TL HRL, PO_LOOKUP_CODES PLC,
RCV_SUPPLY RS
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_NUM IS NOT NULL
AND POV.VENDOR_ID (+) = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID
AND HRL.LOCATION_ID (+) = RSH.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV('LANG')
AND ORG.ORGANIZATION_ID (+) = RSH.ORGANIZATION_ID AND RSH.RECEIPT_SOURCE_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE IN ('RECEIVE','TRANSFER')
ORDER BY 1
SELECT RSH.RECEIPT_NUM RECEIPT_NUM, RSH.CREATION_DATE CREATION_DATE, RT.TRANSACTION_TYPE TRANSACTION_TYPE, NVL(RT.QUANTITY, 0) QUANTITY,rt.destination_type_code destination_type_code,
RT.UNIT_OF_MEASURE UNIT_OF_MEASURE, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION, NVL(RT.PO_UNIT_PRICE, 0) PO_UNIT_PRICE,
RT.CURRENCY_CODE CURRENCY_CODE, NVL(RT.CURRENCY_CONVERSION_RATE,0) RATE, RSL.SHIPMENT_LINE_ID SHIPMENT_LINE_ID,
NVL(RT.QUANTITY * RT.PO_UNIT_PRICE * RT.CURRENCY_CONVERSION_RATE ,0) VALUE
FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_VENDORS POV, PO_VENDOR_SITES POVS,
ORG_ORGANIZATION_DEFINITIONS ORG, HR_LOCATIONS_ALL_TL HRL, PO_LOOKUP_CODES PLC,
RCV_SUPPLY RS
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_NUM IS NOT NULL
AND POV.VENDOR_ID (+) = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID
AND HRL.LOCATION_ID (+) = RSH.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV('LANG')
AND ORG.ORGANIZATION_ID (+) = RSH.ORGANIZATION_ID AND RSH.RECEIPT_SOURCE_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE IN ('RECEIVE','TRANSFER')
ORDER BY 1
No comments:
Post a Comment