VIEW: APPS.MSC_SUPPLIES_OUTBOUND_V

Source

View Text - Preformatted

SELECT msup.sr_instance_id, msup.plan_id, msup.transaction_id, msup.organization_id, mtp.organization_code, msi.inventory_item_id, msi.item_name, msup.revision, msup.unit_number, decode(nvl(msup.disposition_status_type,1),2,'Cancel',null) status, msup.new_schedule_date, msup.last_unit_start_date, (msup.new_order_quantity - nvl(msup.quantity_in_process,0) - nvl(msup.implemented_quantity,0)) order_quantity, msup.old_order_quantity, msup.implement_as LINE_TYPE, msup.supplier_id, DECODE(msup.supplier_id,NULL,NULL, MSC_ST_UTIL.ret_partner_name(msup.supplier_id)) supplier_name, msup.supplier_site_id, DECODE(msup.supplier_site_id,NULL,NULL, MSC_ST_UTIL.ret_partner_site(msup.supplier_site_id)) supplier_site_code, decode(msup.implemented_quantity, null, null, 0), msup.firm_planned_type, decode(msup.firm_planned_type,1,'Yes',2,'No') firm_flag, msup.release_status, msup.new_ship_date, msup.project_id, DECODE(msup.project_id,NULL,NULL,MSC_ST_UTIL.ret_project_number(msup.project_id, msup.sr_instance_id, msup.organization_id, msup.plan_id)) project_number, msup.task_id, DECODE(msup.task_id,NULL,NULL, MSC_ST_UTIL.ret_task_number(msup.project_id, msup.task_id, msup.sr_instance_id, msup.organization_id, msup.plan_id)) task_number, msup.planning_group, msup.demand_class, msi.UOM_CODE, msup.source_supplier_id, msup.source_supplier_site_id FROM msc_trading_partners mtp, msc_supplies msup , msc_apps_instances mai, msc_system_items msi WHERE mtp.sr_tp_id = msup.organization_id AND mtp.sr_instance_id=msi.sr_instance_id AND mtp.partner_type = 3 AND msup.sr_instance_id = mai.instance_id AND msup.inventory_item_id = msi.inventory_item_id AND msup.organization_id = msi.organization_id AND msup.sr_instance_id = msi.sr_instance_id AND msup.plan_id= msi.plan_id AND msup.order_type = 5 AND 0 < (msup.new_order_quantity - nvl(msup.quantity_in_process,0) - nvl(msup.implemented_quantity,0))

View Text - HTML Formatted

SELECT MSUP.SR_INSTANCE_ID
, MSUP.PLAN_ID
, MSUP.TRANSACTION_ID
, MSUP.ORGANIZATION_ID
, MTP.ORGANIZATION_CODE
, MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MSUP.REVISION
, MSUP.UNIT_NUMBER
, DECODE(NVL(MSUP.DISPOSITION_STATUS_TYPE
, 1)
, 2
, 'CANCEL'
, NULL) STATUS
, MSUP.NEW_SCHEDULE_DATE
, MSUP.LAST_UNIT_START_DATE
, (MSUP.NEW_ORDER_QUANTITY - NVL(MSUP.QUANTITY_IN_PROCESS
, 0) - NVL(MSUP.IMPLEMENTED_QUANTITY
, 0)) ORDER_QUANTITY
, MSUP.OLD_ORDER_QUANTITY
, MSUP.IMPLEMENT_AS LINE_TYPE
, MSUP.SUPPLIER_ID
, DECODE(MSUP.SUPPLIER_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_PARTNER_NAME(MSUP.SUPPLIER_ID)) SUPPLIER_NAME
, MSUP.SUPPLIER_SITE_ID
, DECODE(MSUP.SUPPLIER_SITE_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_PARTNER_SITE(MSUP.SUPPLIER_SITE_ID)) SUPPLIER_SITE_CODE
, DECODE(MSUP.IMPLEMENTED_QUANTITY
, NULL
, NULL
, 0)
, MSUP.FIRM_PLANNED_TYPE
, DECODE(MSUP.FIRM_PLANNED_TYPE
, 1
, 'YES'
, 2
, 'NO') FIRM_FLAG
, MSUP.RELEASE_STATUS
, MSUP.NEW_SHIP_DATE
, MSUP.PROJECT_ID
, DECODE(MSUP.PROJECT_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_PROJECT_NUMBER(MSUP.PROJECT_ID
, MSUP.SR_INSTANCE_ID
, MSUP.ORGANIZATION_ID
, MSUP.PLAN_ID)) PROJECT_NUMBER
, MSUP.TASK_ID
, DECODE(MSUP.TASK_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_TASK_NUMBER(MSUP.PROJECT_ID
, MSUP.TASK_ID
, MSUP.SR_INSTANCE_ID
, MSUP.ORGANIZATION_ID
, MSUP.PLAN_ID)) TASK_NUMBER
, MSUP.PLANNING_GROUP
, MSUP.DEMAND_CLASS
, MSI.UOM_CODE
, MSUP.SOURCE_SUPPLIER_ID
, MSUP.SOURCE_SUPPLIER_SITE_ID
FROM MSC_TRADING_PARTNERS MTP
, MSC_SUPPLIES MSUP
, MSC_APPS_INSTANCES MAI
, MSC_SYSTEM_ITEMS MSI
WHERE MTP.SR_TP_ID = MSUP.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MSI.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND MSUP.SR_INSTANCE_ID = MAI.INSTANCE_ID
AND MSUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSUP.PLAN_ID= MSI.PLAN_ID
AND MSUP.ORDER_TYPE = 5
AND 0 < (MSUP.NEW_ORDER_QUANTITY - NVL(MSUP.QUANTITY_IN_PROCESS
, 0) - NVL(MSUP.IMPLEMENTED_QUANTITY
, 0))