MSC_AP_PO_SHIP_RCV_SUPPLY

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
SUPPLY_SOURCE_ID ITEM_ID ORGANIZATION_ID SHIPMENT_HEADER_ID DOCK_DATE SHIPMENT_NUM RCV_TRX_QUANTITY PRIMARY_UOM_QUANTITY PRIMARY_UOM SHIPMENT_LINE_NUM DESTINATION_TYPE_CODE MRP_EXPECTED_DELIVERY_DATE MRP_PRIMARY_QUANTITY MRP_PRIMARY_UOM MRP_DESTINATION_TYPE_CODE MRP_TO_ORGANIZATION_ID MRP_TO_SUBINVENTORY SHIPMENT_LINE_ID ITEM_REVISION EXPECTED_DELIVERY_DATE TO_ORG_PRIMARY_QUANTITY VMI_FLAG FROM_ORGANIZATION_ID TO_ORGANIZATION_ID PROJECT_ID TASK_ID END_ITEM_UNIT_NUMBER TO_SUBINVENTORY

Name

SUPPLY_SOURCE_ID

ITEM_ID

ORGANIZATION_ID

SHIPMENT_HEADER_ID

DOCK_DATE

SHIPMENT_NUM

RCV_TRX_QUANTITY

PRIMARY_UOM_QUANTITY

PRIMARY_UOM

SHIPMENT_LINE_NUM

DESTINATION_TYPE_CODE

MRP_EXPECTED_DELIVERY_DATE

MRP_PRIMARY_QUANTITY

MRP_PRIMARY_UOM

MRP_DESTINATION_TYPE_CODE

MRP_TO_ORGANIZATION_ID

MRP_TO_SUBINVENTORY

SHIPMENT_LINE_ID

ITEM_REVISION

EXPECTED_DELIVERY_DATE

TO_ORG_PRIMARY_QUANTITY

VMI_FLAG

FROM_ORGANIZATION_ID

TO_ORGANIZATION_ID

PROJECT_ID

TASK_ID

END_ITEM_UNIT_NUMBER

TO_SUBINVENTORY

Query

SQL_Statement
SELECT ms.SUPPLY_SOURCE_ID, 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.DISTRIBUTION_QUANTITY / PL.QUANTITY), DECODE(MS.REQ_LINE_ID, NULL, NVL(MS.TO_ORG_PRIMARY_QUANTITY, 0), nvl(MS.TO_ORG_PRIMARY_QUANTITY , 0) * PD.DISTRIBUTION_QUANTITY / PL.QUANTITY), MS.TO_ORG_PRIMARY_UOM_CODE , 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.DISTRIBUTION_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.DISTRIBUTION_QUANTITY / PL.QUANTITY), null, MS.FROM_ORGANIZATION_ID, MS.TO_ORGANIZATION_ID, null, null, null, MS.to_subinventory FROM MSC_AP_EGP_SYSTEM_ITEMS_V msi, POR_REQUISITION_LINES_ALL PL, POR_REQ_DISTRIBUTIONS_ALL PD, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, RCV_TRANSACTIONS RT, INV_SUPPLY MS WHERE MS.SUPPLY_TYPE_CODE = 'RECEIVING' 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 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 MS.PO_DISTRIBUTION_ID IS NULL AND RSL.LINE_NUM IS NOT NULL

SQL_Statement

SELECT

ms.SUPPLY_SOURCE_ID,

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.DISTRIBUTION_QUANTITY / PL.QUANTITY),

DECODE(MS.REQ_LINE_ID,

NULL, NVL(MS.TO_ORG_PRIMARY_QUANTITY, 0),

nvl(MS.TO_ORG_PRIMARY_QUANTITY , 0) * PD.DISTRIBUTION_QUANTITY /

PL.QUANTITY),

MS.TO_ORG_PRIMARY_UOM_CODE ,

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.DISTRIBUTION_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.DISTRIBUTION_QUANTITY /

PL.QUANTITY),

null,

MS.FROM_ORGANIZATION_ID,

MS.TO_ORGANIZATION_ID,

null,

MS.to_subinventory

FROM

MSC_AP_EGP_SYSTEM_ITEMS_V msi,

POR_REQUISITION_LINES_ALL PL,

POR_REQ_DISTRIBUTIONS_ALL PD,

RCV_SHIPMENT_HEADERS RSH,

RCV_SHIPMENT_LINES RSL,

RCV_TRANSACTIONS RT,

INV_SUPPLY MS

WHERE MS.SUPPLY_TYPE_CODE = 'RECEIVING'

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 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 MS.PO_DISTRIBUTION_ID IS NULL

AND RSL.LINE_NUM IS NOT NULL