VIEW: APPS.MSC_TRANSPORTATION_UPDATES_V

Source

View Text - Preformatted

SELECT mtu.rowid row_id, mtu.TRANS_UPDATE_ID, mtu.ebs_sr_instance_id SR_INSTANCE_ID, mtu.otm_domain_name, mtu.ORDER_TYPE order_type, mtu.PO_LINE_LOCATION_ID , mtu.PO_LINE_ID , mtu.WSH_DELIVERY_DETAIL_ID, TO_CHAR(mso.SALES_ORDER_NUMBER) ORDER_NUMBER, msi.INVENTORY_ITEM_ID , msi.item_name Item_segments, mso.ORGANIZATION_ID, msc_get_name.org_code(mso.ORGANIZATION_ID, mso.SR_INSTANCE_ID) Organization_code, mso.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID, mso.SOURCE_ORG_INSTANCE_ID SOURCE_SR_INSTANCE_ID, msc_get_name.org_code(mso.SOURCE_ORGANIZATION_ID, mso.SOURCE_ORG_INSTANCE_ID) Source_org_name, to_number(null) SUPPLIER_ID, null supplier_name, to_number(null) SUPPLIER_SITE_ID, null SUPPLIER_SITE_name, mtu.OTM_RELEASE_LINE_GID , mtu.OTM_RELEASE_GID , mtu.UPDATED_ARRIVAL_DATE , mtu.UPDATED_DUE_DATE , mtu.LAST_UPDATE_DATE , mtu.LAST_UPDATED_BY , mtu.LAST_UPDATE_LOGIN , mtu.CREATION_DATE , mtu.CREATED_BY From MSC_TRANSPORTATION_UPDATES mtu, MSC_DELIVERY_DETAILS mdd, MSC_SALES_ORDERS mso, msc_system_items msi WHERE mtu.WSH_DELIVERY_DETAIL_ID = mdd.DELIVERY_DETAIL_ID and mtu.EBS_SR_INSTANCE_ID = mdd.SR_INSTANCE_ID and mdd.SOURCE_LINE_ID = mso.DEMAND_SOURCE_LINE and mdd.SR_INSTANCE_ID = mso.SR_INSTANCE_ID and mso.ORGANIZATION_ID = msi.ORGANIZATION_ID and mso.SR_INSTANCE_ID = msi.SR_INSTANCE_ID and mso.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID and msi.plan_id=-1 union all select mtu.rowid row_id, mtu.TRANS_UPDATE_ID, mtu.ebs_sr_instance_id SR_INSTANCE_ID, mtu.otm_domain_name, mtu.ORDER_TYPE order_type, mtu.PO_LINE_LOCATION_ID , mtu.PO_LINE_ID , mtu.WSH_DELIVERY_DETAIL_ID, sup.ORDER_NUMBER ORDER_NUMBER, msi.INVENTORY_ITEM_ID , msi.item_name Item_segments, sup.ORGANIZATION_ID, msc_get_name.org_code(sup.ORGANIZATION_ID, sup.SR_INSTANCE_ID) Organization_code, sup.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID, sup.SOURCE_SR_INSTANCE_ID SOURCE_SR_INSTANCE_ID, msc_get_name.org_code(sup.source_organization_id, sup.source_sr_instance_id) SOURCE_ORG_NAME, sup.supplier_id , msc_get_name.supplier(sup.supplier_id) supplier_name, sup.supplier_site_id , msc_get_name.supplier_site(sup.supplier_site_id) supplier_site_name, mtu.OTM_RELEASE_LINE_GID , mtu.OTM_RELEASE_GID , mtu.UPDATED_ARRIVAL_DATE , mtu.UPDATED_DUE_DATE , mtu.LAST_UPDATE_DATE , mtu.LAST_UPDATED_BY , mtu.LAST_UPDATE_LOGIN , mtu.CREATION_DATE , mtu.CREATED_BY From MSC_TRANSPORTATION_UPDATES mtu, MSC_SUPPLIES sup, msc_system_items msi WHERE sup.ORDER_TYPE in (1, 11) And ( sup.PO_LINE_LOCATION_ID = mtu.PO_LINE_LOCATION_ID OR sup.req_line_id = mtu.PO_LINE_LOCATION_ID) AND sup.SR_INSTANCE_ID = mtu.EBS_SR_INSTANCE_ID AND sup.PLAN_ID = msi.plan_id and sup.ORGANIZATION_ID = msi.ORGANIZATION_ID and sup.SR_INSTANCE_ID = msi.SR_INSTANCE_ID and sup.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID and msi.plan_id=-1

View Text - HTML Formatted

SELECT MTU.ROWID ROW_ID
, MTU.TRANS_UPDATE_ID
, MTU.EBS_SR_INSTANCE_ID SR_INSTANCE_ID
, MTU.OTM_DOMAIN_NAME
, MTU.ORDER_TYPE ORDER_TYPE
, MTU.PO_LINE_LOCATION_ID
, MTU.PO_LINE_ID
, MTU.WSH_DELIVERY_DETAIL_ID
, TO_CHAR(MSO.SALES_ORDER_NUMBER) ORDER_NUMBER
, MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NAME ITEM_SEGMENTS
, MSO.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(MSO.ORGANIZATION_ID
, MSO.SR_INSTANCE_ID) ORGANIZATION_CODE
, MSO.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, MSO.SOURCE_ORG_INSTANCE_ID SOURCE_SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MSO.SOURCE_ORGANIZATION_ID
, MSO.SOURCE_ORG_INSTANCE_ID) SOURCE_ORG_NAME
, TO_NUMBER(NULL) SUPPLIER_ID
, NULL SUPPLIER_NAME
, TO_NUMBER(NULL) SUPPLIER_SITE_ID
, NULL SUPPLIER_SITE_NAME
, MTU.OTM_RELEASE_LINE_GID
, MTU.OTM_RELEASE_GID
, MTU.UPDATED_ARRIVAL_DATE
, MTU.UPDATED_DUE_DATE
, MTU.LAST_UPDATE_DATE
, MTU.LAST_UPDATED_BY
, MTU.LAST_UPDATE_LOGIN
, MTU.CREATION_DATE
, MTU.CREATED_BY
FROM MSC_TRANSPORTATION_UPDATES MTU
, MSC_DELIVERY_DETAILS MDD
, MSC_SALES_ORDERS MSO
, MSC_SYSTEM_ITEMS MSI
WHERE MTU.WSH_DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MTU.EBS_SR_INSTANCE_ID = MDD.SR_INSTANCE_ID
AND MDD.SOURCE_LINE_ID = MSO.DEMAND_SOURCE_LINE
AND MDD.SR_INSTANCE_ID = MSO.SR_INSTANCE_ID
AND MSO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSO.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.PLAN_ID=-1 UNION ALL SELECT MTU.ROWID ROW_ID
, MTU.TRANS_UPDATE_ID
, MTU.EBS_SR_INSTANCE_ID SR_INSTANCE_ID
, MTU.OTM_DOMAIN_NAME
, MTU.ORDER_TYPE ORDER_TYPE
, MTU.PO_LINE_LOCATION_ID
, MTU.PO_LINE_ID
, MTU.WSH_DELIVERY_DETAIL_ID
, SUP.ORDER_NUMBER ORDER_NUMBER
, MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NAME ITEM_SEGMENTS
, SUP.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SUP.ORGANIZATION_ID
, SUP.SR_INSTANCE_ID) ORGANIZATION_CODE
, SUP.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID SOURCE_SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(SUP.SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID) SOURCE_ORG_NAME
, SUP.SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(SUP.SUPPLIER_ID) SUPPLIER_NAME
, SUP.SUPPLIER_SITE_ID
, MSC_GET_NAME.SUPPLIER_SITE(SUP.SUPPLIER_SITE_ID) SUPPLIER_SITE_NAME
, MTU.OTM_RELEASE_LINE_GID
, MTU.OTM_RELEASE_GID
, MTU.UPDATED_ARRIVAL_DATE
, MTU.UPDATED_DUE_DATE
, MTU.LAST_UPDATE_DATE
, MTU.LAST_UPDATED_BY
, MTU.LAST_UPDATE_LOGIN
, MTU.CREATION_DATE
, MTU.CREATED_BY
FROM MSC_TRANSPORTATION_UPDATES MTU
, MSC_SUPPLIES SUP
, MSC_SYSTEM_ITEMS MSI
WHERE SUP.ORDER_TYPE IN (1
, 11)
AND ( SUP.PO_LINE_LOCATION_ID = MTU.PO_LINE_LOCATION_ID OR SUP.REQ_LINE_ID = MTU.PO_LINE_LOCATION_ID)
AND SUP.SR_INSTANCE_ID = MTU.EBS_SR_INSTANCE_ID
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.PLAN_ID=-1