1. Tables and Views for SCM
  2. MSC_ORDERS_GOP_V

MSC_ORDERS_GOP_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

DEMAND_ID

CUSTOMER_ID

CUSTOMER_SITE_ID

DEMAND_CLASS

SELLING_PRICE

ORCH_ORDER_HEADER_ID

ORCH_ORDER_LINE_ID

CARRIER_ID

MODE_OF_TRANSPORT

RESERVATION_ID

PLANNED_SHIP_DATE

ITEM

SCHEDULED_ITEM

ORGANIZATION_ID

SCHEDULED_SHIP_FROM_ORG_ID

SHIP_MODEL_COMPLETE_FLAG

ORIGINAL_ITEM_ID

RECEIVING_ORG_ID

CONFIGURED_ITEM

SUPPLIER_ID

SCHEDULED_SUPPLIER_ID

SUPPLIER_SITE_ID

SCHEDULED_SUPPLIER_SITE_ID

ITEM_DESCRIPTION

SCHEDULED_ITEM_DESCRIPTION

USING_REQUIREMENT_QUANTITY

SERVICE_LEVEL

LATEST_ACCEPTABLE_SHIP_DATE

LATEST_ACCEPTABLE_ARRIVAL_DATE

EARLIEST_ACCEPT_SHIP_DATE

EARLIEST_ACCEPT_ARRIVAL_DATE

ORDER_DATE_TYPE_CODE

SHIP_METHOD

SCHEDULE_ARRIVAL_DATE

USING_ASSEMBLY_DEMAND_DATE

ZONE_ID

ROOT_FULFILLMENT_LINE

SHIP_SET_ID

SHIP_SET_NAME

SET_TYPE

SALES_ORDER_PRIORITY

SALES_ORDER_LINE_SPLIT

ISSUED_QUANTITY

FULFILLMENT_COST

SHIPPING_PREFERENCE

UOM_CODE

ORDERED_UOM

UOM_RATE

SALES_ORDER_NUMBER

SO_LINE_NUM

ORDER_NUMBER

LATEST_ACCEPTABLE_DATE

BOM_ITEM_TYPE

BOM_ITEM_TYPE_CODE

ITEM_SUB_TYPE_CODE

ITEM_SUB_TYPE

ORDER_TYPE_CODE

REQUEST_SHIP_DATE

SCHEDULE_SHIP_DATE

SALES_ORDER_LINE_ID

ORDER_MARGIN

EARLIEST_ACCEPTABLE_DATE

PROMISED_ARRIVAL_DATE

PROMISED_SHIP_DATE

REQUESTED_ARRIVAL_DATE

ORDER_LINE_NUMBER

CATEGORY_NAME

ORGANIZATION_NAME

ORGANIZATION

SCHEDULED_SHIP_FROM_WAREHOUSE

SCHEDULED_SHIP_FROM_WARE_NAME

ORDER_TYPE

REQUEST_DATE

REQUEST_DATE2

SCHEDULE_DATE

SCHEDULE_DATE2

CREATION_DATE

DAYS_LATE

ORDER_REVENUE

ORIGINAL_ORG_ID

DEMAND_TYPE

USING_ASSEMBLY_ITEM_ID

ORIGINAL_QUANTITY

SOURCE_ORG_INSTANCE_ID

INSTANCE_CODE

ALLOW_SPLITS

ALLOW_SUBSTITUTION

FULFILLMENT_LINE_NUM

MIN_PERCENTAGE_FOR_SPLIT

MIN_QUANTITY_FOR_SPLIT

ORCH_ORDER_LINE_NUMBER

ORCH_ORDER_NUMBER

PROMISE_DATE

PROMISING_SYSTEM

COMPLETED_QUANTITY

DEMAND_SOURCE_TYPE

SOURCE_DOCUMENT_LINE_NUMBER

SOURCE_DOCUMENT_NUMBER

DEMAND_SOURCE_SYSTEM_ID

INVENTORY_ITEM_ID

CATEGORY_ID

CATEGORY_SET_ID

PARENT_FULFILLMENT_LINE

FULFILLMENT_LINE

CUSTOMER_NAME

CUSTOMER_NUMBER

CUSTOMER_SHIP_SITE

REQUESTED_QUANTITY

SCHEDULED_QUANTITY

SCHEDULED_DROPSHIP_SUPPLIER

SCHEDULED_DROPSHIP_SUP_SITE

FULFILLMENT_MODE

SCHEDULED_CARRIER

SCHEDULED_MODE_OF_TRANSPORT

SCHEDULED_SERVICE_LEVEL

RELEASE_STATUS

OBJECT_VERSION_NUMBER

SUPPLIER_SITE_SOURCE_SYSTEM_ID

SCHED_SUP_SITE_SYSTEM_ID

SCHED_SUP_SITE_SYSTEM

INTERNAL_ORDER_TYPE

GLOBAL_ATTRIBUTE_NUMBER11

GLOBAL_ATTRIBUTE_NUMBER12

GLOBAL_ATTRIBUTE_NUMBER13

GLOBAL_ATTRIBUTE_NUMBER14

GLOBAL_ATTRIBUTE_NUMBER15

GLOBAL_ATTRIBUTE_NUMBER16

GLOBAL_ATTRIBUTE_NUMBER17

GLOBAL_ATTRIBUTE_NUMBER18

GLOBAL_ATTRIBUTE_NUMBER19

GLOBAL_ATTRIBUTE_NUMBER20

GLOBAL_ATTRIBUTE_NUMBER21

GLOBAL_ATTRIBUTE_NUMBER22

GLOBAL_ATTRIBUTE_NUMBER23

GLOBAL_ATTRIBUTE_NUMBER24

GLOBAL_ATTRIBUTE_NUMBER25

GLOBAL_ATTRIBUTE_NUMBER26

GLOBAL_ATTRIBUTE_NUMBER27

GLOBAL_ATTRIBUTE_NUMBER28

GLOBAL_ATTRIBUTE_NUMBER29

GLOBAL_ATTRIBUTE_NUMBER30

GLOBAL_ATTRIBUTE_NUMBER31

GLOBAL_ATTRIBUTE_NUMBER32

GLOBAL_ATTRIBUTE_NUMBER33

GLOBAL_ATTRIBUTE_NUMBER34

GLOBAL_ATTRIBUTE_NUMBER35

GLOBAL_ATTRIBUTE_NUMBER36

GLOBAL_ATTRIBUTE_NUMBER37

GLOBAL_ATTRIBUTE_NUMBER38

GLOBAL_ATTRIBUTE_NUMBER39

GLOBAL_ATTRIBUTE_NUMBER40

Query

SQL_Statement

SELECT DemandsEO.DEMAND_ID,

DemandsEO.CUSTOMER_ID,

nvl(DemandsEO.CUSTOMER_SITE_ID, DemandsEO.SHIP_TO_SITE_ID) AS CUSTOMER_SITE_ID,

DemandsEO.DEMAND_CLASS,

DemandsEO.SELLING_PRICE,

DemandsEO.ORCH_ORDER_HEADER_ID,

DemandsEO.ORCH_ORDER_LINE_ID,

DemandsEO.CARRIER_ID,

DemandsEO.MODE_OF_TRANSPORT,

DemandsEO.RESERVATION_ID,

DemandsEO.PLANNED_SHIP_DATE,

ItemEO.ITEM_NAME AS ITEM,

ItemEO.ITEM_NAME AS SCHEDULED_ITEM,

DemandsEO.ORGANIZATION_ID,

DemandsEO.ORGANIZATION_ID AS SCHEDULED_SHIP_FROM_ORG_ID,

ItemEO.SHIP_MODEL_COMPLETE_FLAG,

DemandsEO.ORIGINAL_ITEM_ID,

DemandsEO.RECEIVING_ORG_ID,

DemandsEO.CONFIGURED_ITEM,

DemandsEO.SUPPLIER_ID,

DemandsEO.SUPPLIER_ID AS SCHEDULED_SUPPLIER_ID,

DemandsEO.SUPPLIER_SITE_ID,

DemandsEO.SUPPLIER_SITE_ID AS SCHEDULED_SUPPLIER_SITE_ID,

ItemEO.DESCRIPTION AS ITEM_DESCRIPTION,

ItemEO.DESCRIPTION AS SCHEDULED_ITEM_DESCRIPTION,

DemandsEO.USING_REQUIREMENT_QUANTITY,

DemandsEO.SERVICE_LEVEL,

DemandsEO.LATEST_ACCEPTABLE_SHIP_DATE,

DemandsEO.LATEST_ACCEPTABLE_ARRIVAL_DATE,

DemandsEO.EARLIEST_ACCEPT_SHIP_DATE,

DemandsEO.EARLIEST_ACCEPT_ARRIVAL_DATE,

DemandsEO.ORDER_DATE_TYPE_CODE,

nvl(DemandsEO.SHIPPING_METHOD_CODE,DemandsEO.SHIP_METHOD) as SHIP_METHOD,

(case WHEN (DemandsEO. supplier_id IS NOT NULL) then DemandsEO. USING_ASSEMBLY_DEMAND_DATE

else DemandsEO.SCHEDULE_ARRIVAL_DATE end) as Schedule_Arrival_Date,

DemandsEO.USING_ASSEMBLY_DEMAND_DATE,

DemandsEO.ZONE_ID,

DemandsEO.ROOT_FULFILLMENT_LINE,

nvl(DemandsEO.SHIP_SET_ID,DemandsEO.ARRIVAL_SET_ID) as SHIP_SET_ID,

nvl(DemandsEO.SHIP_SET_NAME,DemandsEO.ARRIVAL_SET_NAME) as SHIP_SET_NAME,

(CASE when DemandsEO.SHIP_SET_NAME is not null or DemandsEO.SHIP_SET_ID is not null then 1

when DemandsEO.ARRIVAL_SET_NAME is not null or DemandsEO.ARRIVAL_SET_ID is not null then 2

END) as SET_TYPE,

DemandsEO.SALES_ORDER_PRIORITY,

DemandsEO.SALES_ORDER_LINE_SPLIT,

DemandsEO.ISSUED_QUANTITY,

DemandsEO.FULFILLMENT_COST,

DemandsEO.SHIPPING_PREFERENCE,

ItemEO.UOM_CODE as UOM_CODE,

DemandsEO.ORDERED_UOM,

(case when DemandsEO.ORDERED_UOM = ItemEO.UOM_CODE then 1

else msc_gopui_util.uom_conversion_rate(DemandsEO.INVENTORY_ITEM_ID,null,DemandsEO.ORDERED_UOM) end) as UOM_RATE,

DemandsEO.SALES_ORDER_NUMBER,

DemandsEO.SO_LINE_NUM,

(

CASE

WHEN (DemandsEO.orch_order_header_id IS NOT NULL)

THEN nvl(DemandsEO.orch_order_number,DemandsEO.sales_order_number)

ELSE DemandsEO.sales_order_number

END) AS Order_Number,

DECODE( demandsEO.ORDER_DATE_TYPE_CODE, 1, demandsEO.latest_acceptable_ship_date, 2, demandsEO.latest_acceptable_arrival_date) AS Latest_Acceptable_Date,

ItemEO.BOM_ITEM_TYPE,

(

CASE

WHEN (demandsEO.Item_Type_code) IS NOT NULL

THEN

CASE

WHEN DemandsEO.Item_Type_code = 'MODEL' THEN msc_gopui_util.lookup_meaning('ORA_MSC_BOM_ITEM_TYPE',1)

WHEN DemandsEO.Item_Type_code = 'OPTION_CLASS' THEN msc_gopui_util.lookup_meaning('ORA_MSC_BOM_ITEM_TYPE',2)

WHEN DemandsEO.Item_Type_code = 'STANDARD' THEN msc_gopui_util.lookup_meaning('ORA_MSC_BOM_ITEM_TYPE',4)

END

ELSE msc_gopui_util.lookup_meaning('ORA_MSC_BOM_ITEM_TYPE',ItemEO.bom_item_type)

END) AS Bom_item_type_code,

(

CASE

WHEN (DemandsEO.Item_sub_type_code IS NOT NULL)

THEN

CASE

WHEN DemandsEO.item_sub_type_code = 'ATO' THEN 1

WHEN DemandsEO.item_sub_type_code = 'PTO-NONSMC' THEN 2

WHEN DemandsEO.item_sub_type_code = 'PTO-SMC' THEN 3

WHEN DemandsEO.item_sub_type_code = 'OPTION-CLASS' THEN 4

WHEN DemandsEO.item_sub_type_code = 'ATO-OPTION-CLASS' THEN 5

WHEN DemandsEO.item_sub_type_code = 'KIT-SMC' THEN 6

WHEN DemandsEO.item_sub_type_code = 'KIT-NONSMC' THEN 7

WHEN DemandsEO.item_sub_type_code = 'OPTION' THEN 8

WHEN DemandsEO.item_sub_type_code = 'ATO-OPTION' THEN 9

WHEN DemandsEO.item_sub_type_code = 'STANDARD' THEN 10

ELSE 11

END

ELSE

CASE

WHEN (itemEO.bom_item_type = 1

AND itemEO.replenish_to_order_flag = 1

AND ItemEO.pick_components_flag = 2

AND ItemEO.ship_model_complete_flag = 2)

THEN 1

WHEN (ItemEO.bom_item_type = 1

AND ItemEO.replenish_to_order_flag = 2

AND itemEO.pick_components_flag = 1

AND itemEO.ship_model_complete_flag = 2)

THEN 2

WHEN (ItemEO.bom_item_type = 1

AND ItemEO.replenish_to_order_flag = 2

AND ItemEO.pick_components_flag = 1

AND ItemEO.ship_model_complete_flag = 1)

THEN 3

WHEN (ItemEO.bom_item_type = 2

AND ItemEO.replenish_to_order_flag = 2

AND itemEO.pick_components_flag = 1)

THEN 4

WHEN (ItemEO.bom_item_type = 2

AND ItemEO.replenish_to_order_flag = 1

AND itemEO.pick_components_flag = 2 )

THEN 5

WHEN (ItemEO.bom_item_type = 4

AND ItemEO.replenish_to_order_flag = 2

AND ItemEO.pick_components_flag = 1

AND ItemEO.ship_model_complete_flag = 1)

THEN 6

WHEN (ItemEO.bom_item_type = 4

AND ItemEO.replenish_to_order_flag = 2

AND ItemEO.pick_components_flag = 1

AND ItemEO.ship_model_complete_flag = 2)

THEN 7

WHEN (ItemEO.bom_item_type = 3

AND ItemEO.replenish_to_order_flag = 2

AND itemEO.pick_components_flag = 1 )

THEN 8

WHEN (ItemEO.bom_item_type = 3

AND ItemEO.replenish_to_order_flag = 1

AND itemEO.pick_components_flag = 2 )

THEN 9

ELSE 10

END

END ) AS Item_Sub_Type_code,

(

CASE

WHEN (DemandsEO.Item_sub_type_code IS NOT NULL)

THEN

CASE

WHEN DemandsEO.item_sub_type_code = 'ATO' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',1)

WHEN DemandsEO.item_sub_type_code = 'PTO-NONSMC' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',2)

WHEN DemandsEO.item_sub_type_code = 'PTO-SMC' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',3)

WHEN DemandsEO.item_sub_type_code = 'OPTION-CLASS' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',4)

WHEN DemandsEO.item_sub_type_code = 'ATO-OPTION-CLASS' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',5)

WHEN DemandsEO.item_sub_type_code = 'KIT-SMC' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',6)

WHEN DemandsEO.item_sub_type_code = 'KIT-NONSMC' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',7)

WHEN DemandsEO.item_sub_type_code = 'OPTION' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',8)

WHEN DemandsEO.item_sub_type_code = 'ATO-OPTION' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',9)

WHEN DemandsEO.item_sub_type_code = 'STANDARD' THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',10)

ELSE msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',11)

END

ELSE

CASE

WHEN (itemEO.bom_item_type = 1

AND itemEO.replenish_to_order_flag = 1

AND ItemEO.pick_components_flag = 2

AND ItemEO.ship_model_complete_flag = 2)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',1)

WHEN (ItemEO.bom_item_type = 1

AND ItemEO.replenish_to_order_flag = 2

AND itemEO.pick_components_flag = 1

AND itemEO.ship_model_complete_flag = 2)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',2)

WHEN (ItemEO.bom_item_type = 1

AND ItemEO.replenish_to_order_flag = 2

AND ItemEO.pick_components_flag = 1

AND ItemEO.ship_model_complete_flag = 1)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',3)

WHEN (ItemEO.bom_item_type = 2

AND ItemEO.replenish_to_order_flag = 2

AND itemEO.pick_components_flag = 1)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',4)

WHEN (ItemEO.bom_item_type = 2

AND ItemEO.replenish_to_order_flag = 1

AND itemEO.pick_components_flag = 2 )

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',5)

WHEN (ItemEO.bom_item_type = 4

AND ItemEO.replenish_to_order_flag = 2

AND ItemEO.pick_components_flag = 1

AND ItemEO.ship_model_complete_flag = 1)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',6)

WHEN (ItemEO.bom_item_type = 4

AND ItemEO.replenish_to_order_flag = 2

AND ItemEO.pick_components_flag = 1

AND ItemEO.ship_model_complete_flag = 2)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',7)

WHEN (ItemEO.bom_item_type = 3

AND ItemEO.replenish_to_order_flag = 2

AND itemEO.pick_components_flag = 1 )

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',8)

WHEN (ItemEO.bom_item_type = 3

AND ItemEO.replenish_to_order_flag = 1

AND itemEO.pick_components_flag = 2 )

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',9)

ELSE msc_gopui_util.lookup_meaning('ORA_MSC_OP_CTO_ITEM_SUB_TYPE',10)

END

END) AS Item_Sub_Type,

(

CASE

WHEN (demandsEO.orch_order_header_id IS NULL

AND demandsEO.orch_order_line_id IS NULL)

THEN 1

WHEN (demandsEO.orch_order_header_id IS NOT NULL

AND demandsEO.orch_order_line_id IS NOT NULL)

THEN 2

END) AS Order_Type_Code,

DECODE (demandsEO.order_date_type_code, 1, demandsEO.request_date, NULL) AS Request_Ship_Date,

(case WHEN (DemandsEO. supplier_id IS NOT NULL) then DemandsEO. SCHEDULE_SHIP_DATE

else DemandsEO.USING_ASSEMBLY_DEMAND_DATE end) as Schedule_Ship_Date,

(demandsEO.sales_order_line_id) AS Sales_Order_Line_Id,

(demandsEO.using_requirement_quantity*demandsEO.selling_price) - nvl(DemandsEO.fulfillment_cost,0) AS Order_Margin,

DECODE( demandsEO.ORDER_DATE_TYPE_CODE, 1, demandsEO.earliest_accept_ship_date, 2, demandsEO.earliest_accept_arrival_date) AS Earliest_Acceptable_Date,

nvl(demandsEO.promise_arrival_date,DECODE(demandsEO.order_date_type_code,2,demandsEO.promise_date ,NULL)) AS Promised_Arrival_Date,

nvl(demandsEO.promise_ship_date,DECODE (demandsEO.order_date_type_code, 1, demandsEO.promise_date , NULL)) AS Promised_Ship_Date,

DECODE (demandsEO.order_date_type_code, 2, demandsEO.request_date, NULL) AS Requested_Arrival_Date,

( CASE

WHEN (DemandsEO.orch_order_header_id IS NOT NULL)

THEN DemandsEO.orch_order_line_number||'.'||TO_CHAR(Fulfillment_line_num)

ELSE DemandsEO.so_line_num

END) AS Order_Line_Number,

ItemCategoryEO.CATEGORY_NAME,

orgs.organization_name AS ORGANIZATION_NAME,

orgs.organization_code AS ORGANIZATION,

orgs.organization_code AS SCHEDULED_SHIP_FROM_WAREHOUSE,

orgs.organization_name AS SCHEDULED_SHIP_FROM_WARE_NAME,

(

CASE

WHEN (demandsEO.orch_order_header_id IS NULL

AND demandsEO.orch_order_line_id IS NULL)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_ORDER_TYPE',1)

WHEN (demandsEO.orch_order_header_id IS NOT NULL

AND demandsEO.orch_order_line_id IS NOT NULL)

THEN msc_gopui_util.lookup_meaning('ORA_MSC_OP_ORDER_TYPE',2)

END) AS Order_Type,

trunc(DemandsEO.REQUEST_DATE) as request_date,

DemandsEO.REQUEST_DATE as request_date2,

trunc(DECODE( demandsEO.ORDER_DATE_TYPE_CODE, 1, demandsEO.schedule_ship_date, 2, demandsEO.schedule_arrival_date, demandsEO.USING_ASSEMBLY_DEMAND_DATE)) AS Schedule_Date,

DECODE( demandsEO.ORDER_DATE_TYPE_CODE, 1, demandsEO.schedule_ship_date, 2, demandsEO.schedule_arrival_date, demandsEO.USING_ASSEMBLY_DEMAND_DATE) AS Schedule_Date2,

DemandsEO.ORDERED_DATE AS creation_date,

(

CASE

WHEN (demandsEO.days_late IS NOT NULL)

THEN demandsEO.days_late

ELSE (trunc(DECODE( demandsEO.ORDER_DATE_TYPE_CODE, 1, demandsEO.schedule_ship_date, 2, demandsEO.schedule_arrival_date, demandsEO.USING_ASSEMBLY_DEMAND_DATE))- trunc(demandsEO.request_date))

END) AS Days_Late,

(demandsEO.using_requirement_quantity*demandsEO.selling_price) AS Order_Revenue,

DemandsEO.ORIGINAL_ORG_ID,

DemandsEO.DEMAND_TYPE,

DemandsEO.USING_ASSEMBLY_ITEM_ID,

DemandsEO.ORIGINAL_QUANTITY,

(DemandsEO.source_org_instance_id) AS SOURCE_ORG_INSTANCE_ID,

AppInstances.Instance_Code AS INSTANCE_CODE,

DemandsEO.ALLOW_SPLITS,

DemandsEO.ALLOW_SUBSTITUTION,

DemandsEO.FULFILLMENT_LINE_NUM,

DemandsEO.MIN_PERCENTAGE_FOR_SPLIT,

DemandsEO.MIN_QUANTITY_FOR_SPLIT,

DemandsEO.ORCH_ORDER_LINE_NUMBER,

DemandsEO.ORCH_ORDER_NUMBER,

DemandsEO.PROMISE_DATE,

DemandsEO.PROMISING_SYSTEM,

DemandsEO.COMPLETED_QUANTITY,

DemandsEO.DEMAND_SOURCE_TYPE,

decode(DemandsEO.DEMAND_SOURCE_TYPE, 1, DemandsEO.SOURCE_DOCUMENT_LINE_NUMBER, so_line_num) As SOURCE_DOCUMENT_LINE_NUMBER,

decode(DemandsEO.DEMAND_SOURCE_TYPE, 1, DemandsEO.SOURCE_DOCUMENT_NUMBER, sales_order_number) As SOURCE_DOCUMENT_NUMBER,

DemandsEO.DEMAND_SOURCE_SYSTEM_ID,

DemandsEO.INVENTORY_ITEM_ID,

ItemCategoryEO.CATEGORY_ID,

ItemCategoryEO.CATEGORY_SET_ID,

Case when (DemandsEO.PARENT_FULFILLMENT_LINE is not null)

Then Case when (TO_CHAR(DemandsEO.PARENT_FULFILLMENT_LINE) = sales_order_line_id)

Then null

else TO_CHAR(DemandsEO.PARENT_FULFILLMENT_LINE)

end

else TO_CHAR(DemandsEO.PARENT_FULFILLMENT_LINE)

end

AS PARENT_FULFILLMENT_LINE,

sales_order_line_id AS Fulfillment_line,

msc_get_name.customer(DemandsEO.CUSTOMER_ID) AS CUSTOMER_NAME,

msc_get_name.customer_number(DemandsEO.CUSTOMER_ID) AS CUSTOMER_NUMBER,

msc_get_name.customer_site_addr(nvl(DemandsEO.customer_site_id,DemandsEO.ship_to_site_id)) AS CUSTOMER_SHIP_SITE,

DemandsEO.Using_REQUIREMENT_QUANTITY AS REQUESTED_QUANTITY,

DemandsEO.Using_REQUIREMENT_QUANTITY AS SCHEDULED_QUANTITY,

msc_get_name.supplier(DemandsEO.SUPPLIER_ID) AS SCHEDULED_DROPSHIP_SUPPLIER,

msc_get_name.supplier_site(DemandsEO.SUPPLIER_SITE_ID) AS SCHEDULED_DROPSHIP_SUP_SITE,

CASE WHEN (DemandsEO.supplier_id is not null)

Then msc_gopui_util.lookup_meaning('ORA_MSC_OP_FULFILLMENT_MODE',2)

ELSE

decode (ItemEO.back_to_back_flag, 1, msc_gopui_util.lookup_meaning('ORA_MSC_OP_FULFILLMENT_MODE',3), msc_gopui_util.lookup_meaning('ORA_MSC_OP_FULFILLMENT_MODE',1))

END AS FULFILLMENT_MODE ,

CarrierEO.partner_name AS SCHEDULED_CARRIER,

Case when (DemandsEO.mode_of_transport is NOT NULL)

THEN msc_gopui_util.sr_lookup_meaning('WSH_MODE_OF_TRANSPORT',DemandsEO.mode_of_transport) END AS SCHEDULED_MODE_OF_TRANSPORT,

Case when (DemandsEO.service_level is NOT NULL)

THEN msc_gopui_util.sr_lookup_meaning('WSH_SERVICE_LEVELS',DemandsEO.service_level) END AS SCHEDULED_SERVICE_LEVEL,

DemandsEO.release_status,

1 as Object_Version_Number,

DemandsEO.SUPPLIER_SITE_SOURCE_SYSTEM_ID,

DemandsEO.SUPPLIER_SITE_SOURCE_SYSTEM_ID as SCHED_SUP_SITE_SYSTEM_ID,

SourceSystem.instance_code as SCHED_SUP_SITE_SYSTEM,

case

when (DEMANDSEO.demand_source_type = 1 and DEMANDSEO.expense_transfer = 1) then 'TOE'

when (DEMANDSEO.demand_source_type = 1 and (DEMANDSEO.expense_transfer = 2 or DEMANDSEO.expense_transfer is null )) then 'TO'

when (DEMANDSEO.demand_source_type = 8 and DEMANDSEO.expense_transfer = 1) then 'ISOE'

when (DEMANDSEO.demand_source_type = 8 and (DEMANDSEO.expense_transfer = 2 or DEMANDSEO.expense_transfer is null )) then 'ISO'

end as INTERNAL_ORDER_TYPE,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER11,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER12,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER13,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER14,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER15,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER16,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER17,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER18,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER19,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER20,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER21,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER22,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER23,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER24,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER25,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER26,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER27,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER28,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER29,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER30,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER31,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER32,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER33,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER34,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER35,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER36,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER37,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER38,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER39,

DemandsEO.GLOBAL_ATTRIBUTE_NUMBER40

FROM MSC_DEMANDS DemandsEO,

MSC_SYSTEM_ITEMS_V ItemEO,

msc_parameters orgs,

msc_apps_instances AppInstances,

msc_apps_instances SourceSystem,

(SELECT CategoryEO.CATEGORY_ID,

CategoryEO.CATEGORY_SET_ID,

CategoryEO.CATEGORY_NAME,

ItemCategoriesEO.inventory_item_id,

ItemCategoriesEO.organization_id

FROM MSC_CATALOG_CATEGORIES CategoryEO,

MSC_ITEM_CATEGORIES ItemCategoriesEO

WHERE CategoryEO.category_set_id = NVL(fnd_profile.value('MSC_SRC_ASSIGNMENT_CATALOG'),2)

AND CategoryEO.category_id = ItemCategoriesEO.category_id

) ItemCategoryEO,

(SELECT GlobalTradingPartnerEO.TP_ID,

GlobalTradingPartnerEO.PARTNER_TYPE,

GlobalTradingPartnerEO.PARTNER_NAME

FROM MSC_GLOBAL_TRADING_PARTNERS GlobalTradingPartnerEO

WHERE partner_type =4) CarrierEO

WHERE DemandsEO.origination_type IN (30,1030)

AND DemandsEO.plan_id = -1

AND ItemEO.plan_id = -1

AND ItemEO.Inventory_item_id = DemandsEO.Inventory_item_id

AND ItemEO.Organization_id = DemandsEO.Organization_id

AND ItemCategoryEO.inventory_item_id(+) = DemandsEO.inventory_item_id

AND ItemCategoryEO.organization_id(+) = DemandsEO.organization_id

AND orgs.organization_id(+) = DemandsEO.organization_id

AND DemandsEO.demand_source_system_id = AppInstances.instance_id(+)

AND DemandsEO.supplier_site_source_system_id = SourceSystem.instance_id(+)

AND DEMANDSEO.carrier_id = CarrierEO.TP_ID(+)

AND DemandsEO.Using_REQUIREMENT_QUANTITY > 0

AND DemandsEO.USING_ASSEMBLY_DEMAND_DATE is not null

AND (DemandsEO.inquiry_demand is null or DemandsEO.inquiry_demand = 2)

And (DEMANDSEO.expense_transfer is null or DEMANDSEO.expense_transfer=2 or DEMANDSEO.expense_transfer=1 and DEMANDSEO.interfaced_to_doo=1)