VIEW: APPS.PO_SHIP_RCV_SUPPLY_VIEW

Source

View Text - Preformatted

SELECT MS.ITEM_ID , MS.TO_ORGANIZATION_ID , MS.SHIPMENT_HEADER_ID , RT.TRANSACTION_DATE , RSH.SHIPMENT_NUM , DECODE(MS.REQ_LINE_ID, NULL, ms.QUANTITY, nvl(MS.QUANTITY , 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY), MUOM.UOM_CODE , DECODE(MS.REQ_LINE_ID, NULL, NVL(MS.TO_ORG_PRIMARY_QUANTITY, 0), nvl(MS.TO_ORG_PRIMARY_QUANTITY , 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY), MS.TO_ORG_PRIMARY_UOM , RSL.LINE_NUM , MS.DESTINATION_TYPE_CODE , MS.MRP_EXPECTED_DELIVERY_DATE , DECODE(MS.REQ_LINE_ID, NULL, nvl(MS.MRP_PRIMARY_QUANTITY , 0), nvl(MS.MRP_PRIMARY_QUANTITY , 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY), MS.mrp_primary_uom , MS.mrp_destination_type_code , MS.mrp_to_organization_id , MS.mrp_to_subinventory , MS.shipment_line_id , MS.ITEM_REVISION ,ms.EXPECTED_DELIVERY_DATE , DECODE(MS.REQ_LINE_ID, NULL, nvl(ms.TO_ORG_PRIMARY_QUANTITY , 0) , nvl(ms.TO_ORG_PRIMARY_QUANTITY , 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY), ms.TO_ORGANIZATION_ID, DECODE(MS.REQ_LINE_ID, NULL, TO_NUMBER(NULL), PD.PROJECT_ID), DECODE(MS.REQ_LINE_ID, NULL, TO_NUMBER(NULL), PD.TASK_ID ), DECODE(MS.REQ_LINE_ID, NULL, substr(po_uniteff_pkg.get_unit_number(MS.shipment_line_id, MS.item_id), 0, 30), PD.END_ITEM_UNIT_NUMBER) ,ms.to_subinventory ,ms.from_organization_id FROM MTL_UNITS_OF_MEASURE MUOM, MTL_SUPPLY MS, PO_REQUISITION_LINES_ALL PL, PO_REQ_DISTRIBUTIONS_ALL PD, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, RCV_TRANSACTIONS RT WHERE MS.SUPPLY_TYPE_CODE = 'RECEIVING' AND MS.REQ_LINE_ID = PL.REQUISITION_LINE_ID(+) AND MS.REQ_LINE_ID = PD.REQUISITION_LINE_ID(+) AND RSH.SHIPMENT_HEADER_ID = MS.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = MS.SHIPMENT_LINE_ID AND RT.TRANSACTION_ID = MS.RCV_TRANSACTION_ID AND DECODE(MS.PO_DISTRIBUTION_ID,NULL,1, PL.QUANTITY) > 0 AND MUOM.UNIT_OF_MEASURE = MS.UNIT_OF_MEASURE AND MS.PO_DISTRIBUTION_ID IS NULL

View Text - HTML Formatted

SELECT MS.ITEM_ID
, MS.TO_ORGANIZATION_ID
, MS.SHIPMENT_HEADER_ID
, RT.TRANSACTION_DATE
, RSH.SHIPMENT_NUM
, DECODE(MS.REQ_LINE_ID
, NULL
, MS.QUANTITY
, NVL(MS.QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MUOM.UOM_CODE
, DECODE(MS.REQ_LINE_ID
, NULL
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0)
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MS.TO_ORG_PRIMARY_UOM
, RSL.LINE_NUM
, MS.DESTINATION_TYPE_CODE
, MS.MRP_EXPECTED_DELIVERY_DATE
, DECODE(MS.REQ_LINE_ID
, NULL
, NVL(MS.MRP_PRIMARY_QUANTITY
, 0)
, NVL(MS.MRP_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MS.MRP_PRIMARY_UOM
, MS.MRP_DESTINATION_TYPE_CODE
, MS.MRP_TO_ORGANIZATION_ID
, MS.MRP_TO_SUBINVENTORY
, MS.SHIPMENT_LINE_ID
, MS.ITEM_REVISION
, MS.EXPECTED_DELIVERY_DATE
, DECODE(MS.REQ_LINE_ID
, NULL
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0)
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MS.TO_ORGANIZATION_ID
, DECODE(MS.REQ_LINE_ID
, NULL
, TO_NUMBER(NULL)
, PD.PROJECT_ID)
, DECODE(MS.REQ_LINE_ID
, NULL
, TO_NUMBER(NULL)
, PD.TASK_ID )
, DECODE(MS.REQ_LINE_ID
, NULL
, SUBSTR(PO_UNITEFF_PKG.GET_UNIT_NUMBER(MS.SHIPMENT_LINE_ID
, MS.ITEM_ID)
, 0
, 30)
, PD.END_ITEM_UNIT_NUMBER)
, MS.TO_SUBINVENTORY
, MS.FROM_ORGANIZATION_ID
FROM MTL_UNITS_OF_MEASURE MUOM
, MTL_SUPPLY MS
, PO_REQUISITION_LINES_ALL PL
, PO_REQ_DISTRIBUTIONS_ALL PD
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
WHERE MS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND MS.REQ_LINE_ID = PL.REQUISITION_LINE_ID(+)
AND MS.REQ_LINE_ID = PD.REQUISITION_LINE_ID(+)
AND RSH.SHIPMENT_HEADER_ID = MS.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = MS.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = MS.RCV_TRANSACTION_ID
AND DECODE(MS.PO_DISTRIBUTION_ID
, NULL
, 1
, PL.QUANTITY) > 0
AND MUOM.UNIT_OF_MEASURE = MS.UNIT_OF_MEASURE
AND MS.PO_DISTRIBUTION_ID IS NULL