View: MRP_AP_PO_SHIP_SUPPLY_V

View Text

SELECT MS.TRANSACTION_ID
, MS.ITEM_ID
, MS.TO_ORGANIZATION_ID
, MS.SHIPMENT_HEADER_ID
, MS.RECEIPT_DATE
, RSH.SHIPMENT_NUM
, DECODE( MS.REQ_LINE_ID
, NULL
, MS.QUANTITY
, NVL(MS.QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, DECODE( MS.REQ_LINE_ID
, NULL
, MS.TO_ORG_PRIMARY_QUANTITY
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0)*PD.REQ_LINE_QUANTITY /PL.QUANTITY)
, RSL.LINE_NUM
, MS.DESTINATION_TYPE_CODE
, MS.MRP_EXPECTED_DELIVERY_DATE
, DECODE( MS.REQ_LINE_ID
, NULL
, MS.MRP_PRIMARY_QUANTITY
, 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
, MS.TO_ORG_PRIMARY_QUANTITY
, 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
, MSI.SHRINKAGE_RATE
, MS.RN RN3
, MSI.RN RN2
, MP.RN RN1
FROM MRP_AP_MRP_PARAS_SN MP
, MRP_AP_SYS_ITEMS_SN MSI
, MRP_AP_RCV_SHDRS_SN RSH
, MRP_AP_RCV_SLINES_SN RSL
, MRP_AP_PO_REQLINE_SN PL
, MRP_AP_PO_REQDIST_SN PD
, MRP_AP_SUPPLY_SN MS
WHERE MS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND MP.ORGANIZATION_ID= MS.TO_ORGANIZATION_ID
AND MSI.ORGANIZATION_ID= MS.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID= MS.ITEM_ID
AND MS.REQ_LINE_ID = PL.REQUISITION_LINE_ID (+)
AND MS.REQ_LINE_ID = PD.REQUISITION_LINE_ID (+)
AND MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND MS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND DECODE(REQ_LINE_ID
, NULL
, 1
, PL.QUANTITY) > 0
AND RSL.LINE_NUM IS NOT NULL
AND MS.SHIPMENT_LINE_ID IS NOT NULL

Columns