Wednesday, November 22, 2017

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

No comments:

Post a Comment