1. Tables and Views for SCM
  2. MSC_ATP_SUPPLIES_V

MSC_ATP_SUPPLIES_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

ORDER_TYPE

TRANSACTION_ID

ORDER_NUMBER

INVENTORY_ITEM_ID

ORGANIZATION_ID

NEW_ORDER_QUANTITY

SUPPLIER_ID

SUPPLIER_SITE_ID

DROP_SHIP_CUST_ID

DROP_SHIP_CUST_SITE_ID

PRIMARY_UOM_CODE

SHRINKAGE_RATE

DOCK_DATE

SCHEDULE_DATE

SHIP_DATE

SHIP_METHOD

SOURCE_ORGANIZATION_ID

ROUTING_SEQUENCE_ID

BILL_SEQUENCE_ID

DISPOSITION_ID

REFRESH_NUMBER

PLAN_ID

INCLUDE_DEPENDENT_WIP_DEMAND

FULFILL_ORCHESTRATION_REQUIRED

FG_ORDER_TYPE

Query

SQL_Statement

SELECT /*+ INDEX(sup MSC_SUPPLIES_N5) */

sup.order_type order_type,

sup.transaction_id transaction_id,

sup.order_number order_number,

sup.inventory_item_id inventory_item_id,

sup.organization_id organization_id,

(case when nvl(sup.new_order_quantity,0) <= 0 then 0

else sup.new_order_quantity end) AS new_order_quantity,

sup.supplier_id supplier_id,

sup.supplier_site_id supplier_site_id,

sup.drop_ship_cust_id drop_ship_cust_id,

sup.drop_ship_cust_site_id drop_ship_cust_site_id,

sn.uom_code primary_uom_code,

sn.shrinkage_rate shrinkage_rate,

nvl(sup.new_dock_date,nvl(sup.new_ship_date,sup.new_schedule_date)) dock_date,

sup.new_schedule_date schedule_date,

nvl(sup.new_ship_date,sup.new_schedule_date) ship_date,

decode(sup.carrier_id || ':' ||sup.mode_of_transport || ':' ||sup.service_level,'::',to_char(null),sup.carrier_id || ':' ||sup.mode_of_transport || ':' || sup.service_level) ship_method ,

sup.source_organization_id source_organization_id,

sup.routing_sequence_id routing_sequence_id,

sup.bill_sequence_id bill_sequence_id,

sup.disposition_id disposition_id,

sup.refresh_number refresh_number,

sup.plan_id,

sn.include_dependent_wip_demand,

sup.fulfill_orchestration_required,

sup.fg_order_type

FROM msc_suppliesrc_v sup,

msc_atp_rule_v sn

WHERE sup.plan_id = -1

AND sn.organization_id = sup.organization_id

AND sn.inventory_item_id = sup.inventory_item_id

AND(

(sup.order_type = decode(nvl(sn.include_onhand_available, 2),1,18, -1))

OR(sup.order_type = decode(nvl(sn.include_onhand_available, 2),1, 31,-1))

OR(sup.order_type = decode(nvl(sn.include_user_defined_supply,2), 1,41, -1))

OR(sup.order_type = decode(nvl(sn.include_purchase_orders,2),1, 1,-1))

OR((sup.order_type = decode(nvl(sn.include_purchase_orders,2),1, 8,-1)) AND sup.fg_order_type = 8000)

OR((sup.order_type = decode(nvl(sn.include_purchase_orders,2),1, 11, -1)) AND sup.fg_order_type = 1100)

OR(sup.order_type = decode(nvl(sn.include_purchase_orders,2),1, 49,-1))

OR (sup.order_type = decode(nvl(sn.include_interorg_transfers,2), 1, 94,-1))

OR((sup.order_type = decode(nvl(sn.include_interorg_transfers,2),1, 8,-1)) AND sup.fg_order_type = 9408)

OR((sup.order_type = decode(nvl(sn.include_interorg_transfers,2),1, 11, -1)) AND sup.fg_order_type = 9411)

OR (sup.order_type = decode(nvl(sn.include_interorg_transfers,2), 1, 12,-1))

OR (sup.order_type = decode(nvl(sn.include_internal_reqs,2), 1, 53,-1))

OR (sup.order_type = decode(nvl(sn.INCLUDE_DISCRETE_WIP_RECEIPTS,2),1,3,-1))

OR (sup.order_type = decode(nvl(sn.INCLUDE_NONSTD_WIP_RECEIPTS,2),1,7,-1))

OR (sup.order_type = decode(nvl(sn.INCLUDE_FLOW_SCHEDULE_RECEIPTS,2),1,27,-1))

OR (sup.order_type = decode(nvl(sn.include_requisition,2),1,2,-1))

OR (sup.order_type = decode(nvl(sn.include_planned_supply,2), 1, 5,-1))

)

AND nvl(sn.lead_time_code,2) = 2

AND nvl(sn.infinite_available_code,2) = 2

AND exists

(select 1

from

MSC_SUPPLY_UPDATE_ASGN_V assgn

where

assgn.organization_id = sup.organization_id

AND assgn.inventory_item_id = sup.inventory_item_id

)

AND (sup.subinventory_code IS NULL

OR

exists (select 'x' from msc_sub_inventories sub

WHERE sub.plan_id = -1

AND sub.organization_id = sup.organization_id

AND sub.sub_inventory_code = sup.subinventory_code

AND nvl(sub.inventory_atp_code,1) <> 2))