INV_RSV_TREETABLE_DEMAND

Details

Schema: FUSION
Object owner: INV
Object type: VIEW

Columns

Name
ORGANIZATION_ID INVENTORY_ITEM_ID DEMAND_SOURCE_TYPE_ID DEMAND_SOURCE_HEADER_ID DEMAND_SOURCE_LINE_ID DEMAND_SOURCE_NAME DEMAND_SOURCE_LINE_NUMBER DEMAND_SOURCE_HEADER_NUMBER ORDERED_QUANTITY ORDERED_UOM_CODE PRIMARY_DEMAND_QUANTITY PRIMARY_UOM_CODE CUSTOMER_ID DUE_DATE DEMAND_PROJECT_ID DEMAND_TASK_ID ASSEMBLY_ITEM_ID OPERATION_SEQ_NUMBER ORDER_INTERFACE_STATUS

Name

ORGANIZATION_ID

INVENTORY_ITEM_ID

DEMAND_SOURCE_TYPE_ID

DEMAND_SOURCE_HEADER_ID

DEMAND_SOURCE_LINE_ID

DEMAND_SOURCE_NAME

DEMAND_SOURCE_LINE_NUMBER

DEMAND_SOURCE_HEADER_NUMBER

ORDERED_QUANTITY

ORDERED_UOM_CODE

PRIMARY_DEMAND_QUANTITY

PRIMARY_UOM_CODE

CUSTOMER_ID

DUE_DATE

DEMAND_PROJECT_ID

DEMAND_TASK_ID

ASSEMBLY_ITEM_ID

OPERATION_SEQ_NUMBER

ORDER_INTERFACE_STATUS

Query

SQL_Statement
SELECT MR.ORGANIZATION_ID, MR.INVENTORY_ITEM_ID, MR.DEMAND_SOURCE_TYPE_ID, MR.DEMAND_SOURCE_HEADER_ID, MR.DEMAND_SOURCE_LINE_ID, MR.DEMAND_SOURCE_NAME, /*** Change it later when flexible supply demand is completed / to_char(MR.DEMAND_SOURCE_LINE_ID) AS DEMAND_SOURCE_LINE_NUMBER, to_char(MR.DEMAND_SOURCE_HEADER_ID) AS DEMAND_SOURCE_HEADER_NUMBER, NVL(DFL.ORDERED_QTY, MR.PRIMARY_RESERVATION_QUANTITY) AS ORDERED_QUANTITY, NVL(DFL.ORDERED_UOM,MR.PRIMARY_UOM_CODE) AS ORDERED_UOM_CODE, (inv_decimals_pub.get_primary_quantity( MR.ORGANIZATION_ID, MR.INVENTORY_ITEM_ID, NVL(DFL.ORDERED_UOM,MR.PRIMARY_UOM_CODE), NVL(DFL.ORDERED_QTY,MR.PRIMARY_RESERVATION_QUANTITY) )) AS PRIMARY_DEMAND_QUANTITY, NVL(DFL.ORDERED_UOM,MR.PRIMARY_UOM_CODE ) AS PRIMARY_UOM_CODE, /* Changed it Later Based on flexible Supply Demand Project / NULL AS CUSTOMER_ID, MR.REQUIREMENT_DATE AS DUE_DATE, DPV.PJC_PROJECT_ID AS DEMAND_PROJECT_ID, DPV.PJC_TASK_ID AS DEMAND_TASK_ID, NULL AS ASSEMBLY_ITEM_ID, NULL AS OPERATION_SEQ_NUMBER, NVL(MR.ORDER_INTERFACE_STATUS, 'Y') ORDER_INTERFACE_STATUS FROM INV_RESERVATIONS MR, DOO_PROJECTS_V DPV,DOO_FULFILL_LINES_ALL DFL where mr.demand_source_type_id IN (2,8,18) AND mr.demand_source_line_id = DPV.parent_entity_id(+) AND DPV.parent_entity_code(+) = 'LINE' AND DFL.FULFILL_LINE_ID(+) = MR.DEMAND_SOURCE_LINE_ID AND not exists ( Select '1' from WSH_DELIVERY_DETAILS WDD where WDD.source_header_id = MR.demand_source_header_id and WDD.source_line_id = MR.demand_source_line_id and WDD.organization_id = MR.organization_id ) UNION ALL SELECT MR.ORGANIZATION_ID, MR.INVENTORY_ITEM_ID, MR.DEMAND_SOURCE_TYPE_ID, MR.DEMAND_SOURCE_HEADER_ID, MR.DEMAND_SOURCE_LINE_ID, MR.DEMAND_SOURCE_NAME, /* Change it later when flexible supply demand is completed / to_char(MR.DEMAND_SOURCE_LINE_ID) AS DEMAND_SOURCE_LINE_NUMBER, to_char(MR.DEMAND_SOURCE_HEADER_ID) AS DEMAND_SOURCE_HEADER_NUMBER, MR.PRIMARY_RESERVATION_QUANTITY AS ORDERED_QUANTITY, MR.PRIMARY_UOM_CODE AS ORDERED_UOM_CODE, MR.PRIMARY_RESERVATION_QUANTITY AS PRIMARY_DEMAND_QUANTITY, MR.PRIMARY_UOM_CODE AS PRIMARY_UOM_CODE, /* Changed it Later Based on flexible Supply Demand Project */ NULL AS CUSTOMER_ID, MR.REQUIREMENT_DATE AS DUE_DATE, NULL AS DEMAND_PROJECT_ID, NULL AS DEMAND_TASK_ID, NULL AS ASSEMBLY_ITEM_ID, NULL AS OPERATION_SEQ_NUMBER, NULL AS ORDER_INTERFACE_STATUS FROM INV_RESERVATIONS MR where mr.demand_source_type_id in (3,6,9,13) / The below query fetched data when it is already interfaced / / Pick up from MTL_RESERVATIONS ONLY IF IT IS NOT FOUND IN WDD / UNION ALL SELECT WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID, to_number(MSO.SOURCE_ORDER_TYPE_ID) AS DEMAND_SOURCE_TYPE_ID, WDD.SOURCE_HEADER_ID AS DEMAND_SOURCE_HEADER_ID, WDD.SOURCE_LINE_ID AS DEMAND_SOURCE_LINE_ID, NULL AS DEMAND_SOURCE_NAME, WDD.SOURCE_LINE_NUMBER AS DEMAND_SOURCE_LINE_NUMBER, WDD.SOURCE_HEADER_NUMBER AS DEMAND_SOURCE_HEADER_NUMBER, /** Order Details / WDD.SRC_REQUESTED_QUANTITY AS ORDERED_QUANTITY, WDD.SRC_REQUESTED_QUANTITY_UOM AS ORDERED_UOM_CODE, /** Need to be Converted Later to Primary Quantity / ( inv_decimals_pub.get_primary_quantity( WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID, WDD.SRC_REQUESTED_QUANTITY_UOM, WDD.SRC_REQUESTED_QUANTITY )) AS PRIMARY_DEMAND_QUANTITY, MSI.PRIMARY_UOM_CODE AS PRIMARY_UOM_CODE, WDD.SOLD_TO_PARTY_ID AS CUSTOMER_ID, WDD.DATE_SCHEDULED AS DUE_DATE, WDD.PJC_PROJECT_ID AS DEMAND_PROJECT_ID, WDD.PJC_TASK_ID AS DEMAND_TASK_ID, NULL AS ASSEMBLY_ITEM_ID, NULL AS OPERATION_SEQ_NUMBER, 'Y' AS ORDER_INTERFACE_STATUS FROM WSH_DELIVERY_DETAILS WDD, EGP_SYSTEM_ITEMS_VL MSI, INV_SALES_ORDERS MSO WHERE WDD.INV_INTERFACED_FLAG <> 'Y' AND WDD.SOURCE_HEADER_ID = MSO.SALES_ORDER_ID AND MSI.inventory_item_id = WDD.inventory_item_id AND WDD.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL / Work Order Demand*/ SELECT WO.ORGANIZATION_ID, WO.COMPONENT_ITEM_ID AS INVENTORY_ITEM_ID, 5 DEMAND_SOURCE_TYPE_ID, WO.WORK_ORDER_ID AS DEMAND_SOURCE_HEADER_ID, WO.WO_OPERATION_ID AS DEMAND_SOURCE_LINE_ID, NULL AS DEMAND_SOURCE_NAME, NULL AS DEMAND_SOURCE_LINE_NUMBER, WO.WORK_ORDER_NUMBER AS DEMAND_SOURCE_HEADER_NUMBER, WO.REQUESTED_QUANTITY AS ORDERED_QUANTITY, WO.DEMAND_UOM AS ORDERED_UOM_CODE, (inv_decimals_pub.get_primary_quantity( WO.ORGANIZATION_ID, WO.COMPONENT_ITEM_ID, WO.DEMAND_UOM, WO.REQUESTED_QUANTITY )) AS PRIMARY_DEMAND_QUANTITY, WO.DEMAND_UOM AS PRIMARY_UOM_CODE, NULL AS CUSTOMER_ID, WO.REQUIRED_DATE AS DUE_DATE, WO.DEMAND_PROJECT_ID, WO.DEMAND_TASK_ID, WO.ASSEMBLY_ITEM_ID AS ASSEMBLY_ITEM_ID, WO.OPERATION_SEQ_NUMBER AS OPERATION_SEQ_NUMBER, NULL AS ORDER_INTERFACE_STATUS FROM WIE_WO_RESERVABLE_COMPS_V WO

SQL_Statement

SELECT

MR.ORGANIZATION_ID,

MR.INVENTORY_ITEM_ID,

MR.DEMAND_SOURCE_TYPE_ID,

MR.DEMAND_SOURCE_HEADER_ID,

MR.DEMAND_SOURCE_LINE_ID,

MR.DEMAND_SOURCE_NAME,

/*** Change it later when flexible supply demand is completed */

to_char(MR.DEMAND_SOURCE_LINE_ID) AS DEMAND_SOURCE_LINE_NUMBER,

to_char(MR.DEMAND_SOURCE_HEADER_ID) AS DEMAND_SOURCE_HEADER_NUMBER,

NVL(DFL.ORDERED_QTY, MR.PRIMARY_RESERVATION_QUANTITY) AS ORDERED_QUANTITY,

NVL(DFL.ORDERED_UOM,MR.PRIMARY_UOM_CODE) AS ORDERED_UOM_CODE,

(inv_decimals_pub.get_primary_quantity(

MR.ORGANIZATION_ID,

MR.INVENTORY_ITEM_ID,

NVL(DFL.ORDERED_UOM,MR.PRIMARY_UOM_CODE),

NVL(DFL.ORDERED_QTY,MR.PRIMARY_RESERVATION_QUANTITY)

)) AS PRIMARY_DEMAND_QUANTITY,

NVL(DFL.ORDERED_UOM,MR.PRIMARY_UOM_CODE ) AS PRIMARY_UOM_CODE,

/** Changed it Later Based on flexible Supply Demand Project **/

NULL AS CUSTOMER_ID,

MR.REQUIREMENT_DATE AS DUE_DATE,

DPV.PJC_PROJECT_ID AS DEMAND_PROJECT_ID,

DPV.PJC_TASK_ID AS DEMAND_TASK_ID,

NULL AS ASSEMBLY_ITEM_ID,

NULL AS OPERATION_SEQ_NUMBER,

NVL(MR.ORDER_INTERFACE_STATUS, 'Y') ORDER_INTERFACE_STATUS

FROM INV_RESERVATIONS MR, DOO_PROJECTS_V DPV,DOO_FULFILL_LINES_ALL DFL

where mr.demand_source_type_id IN (2,8,18)

AND mr.demand_source_line_id = DPV.parent_entity_id(+)

AND DPV.parent_entity_code(+) = 'LINE'

AND DFL.FULFILL_LINE_ID(+) = MR.DEMAND_SOURCE_LINE_ID

AND not exists (

Select '1'

from WSH_DELIVERY_DETAILS WDD

where WDD.source_header_id = MR.demand_source_header_id

and WDD.source_line_id = MR.demand_source_line_id

and WDD.organization_id = MR.organization_id

)

UNION ALL

SELECT

MR.ORGANIZATION_ID,

MR.INVENTORY_ITEM_ID,

MR.DEMAND_SOURCE_TYPE_ID,

MR.DEMAND_SOURCE_HEADER_ID,

MR.DEMAND_SOURCE_LINE_ID,

MR.DEMAND_SOURCE_NAME,

/*** Change it later when flexible supply demand is completed */

to_char(MR.DEMAND_SOURCE_LINE_ID) AS DEMAND_SOURCE_LINE_NUMBER,

to_char(MR.DEMAND_SOURCE_HEADER_ID) AS DEMAND_SOURCE_HEADER_NUMBER,

MR.PRIMARY_RESERVATION_QUANTITY AS ORDERED_QUANTITY,

MR.PRIMARY_UOM_CODE AS ORDERED_UOM_CODE,

MR.PRIMARY_RESERVATION_QUANTITY AS PRIMARY_DEMAND_QUANTITY,

MR.PRIMARY_UOM_CODE AS PRIMARY_UOM_CODE,

/** Changed it Later Based on flexible Supply Demand Project **/

NULL AS CUSTOMER_ID,

MR.REQUIREMENT_DATE AS DUE_DATE,

NULL AS DEMAND_PROJECT_ID,

NULL AS DEMAND_TASK_ID,

NULL AS ASSEMBLY_ITEM_ID,

NULL AS OPERATION_SEQ_NUMBER,

NULL AS ORDER_INTERFACE_STATUS

FROM INV_RESERVATIONS MR

where mr.demand_source_type_id in (3,6,9,13)

/* The below query fetched data when it is already interfaced */

/*** Pick up from MTL_RESERVATIONS ONLY IF IT IS NOT FOUND IN WDD ***/

UNION ALL

SELECT

WDD.ORGANIZATION_ID,

WDD.INVENTORY_ITEM_ID,

to_number(MSO.SOURCE_ORDER_TYPE_ID) AS DEMAND_SOURCE_TYPE_ID,

WDD.SOURCE_HEADER_ID AS DEMAND_SOURCE_HEADER_ID,

WDD.SOURCE_LINE_ID AS DEMAND_SOURCE_LINE_ID,

NULL AS DEMAND_SOURCE_NAME,

WDD.SOURCE_LINE_NUMBER AS DEMAND_SOURCE_LINE_NUMBER,

WDD.SOURCE_HEADER_NUMBER AS DEMAND_SOURCE_HEADER_NUMBER,

/*** Order Details */

WDD.SRC_REQUESTED_QUANTITY AS ORDERED_QUANTITY,

WDD.SRC_REQUESTED_QUANTITY_UOM AS ORDERED_UOM_CODE,

/*** Need to be Converted Later to Primary Quantity */

( inv_decimals_pub.get_primary_quantity(

WDD.ORGANIZATION_ID,

WDD.INVENTORY_ITEM_ID,

WDD.SRC_REQUESTED_QUANTITY_UOM,

WDD.SRC_REQUESTED_QUANTITY

)) AS PRIMARY_DEMAND_QUANTITY,

MSI.PRIMARY_UOM_CODE AS PRIMARY_UOM_CODE,

WDD.SOLD_TO_PARTY_ID AS CUSTOMER_ID,

WDD.DATE_SCHEDULED AS DUE_DATE,

WDD.PJC_PROJECT_ID AS DEMAND_PROJECT_ID,

WDD.PJC_TASK_ID AS DEMAND_TASK_ID,

NULL AS ASSEMBLY_ITEM_ID,

NULL AS OPERATION_SEQ_NUMBER,

'Y' AS ORDER_INTERFACE_STATUS

FROM WSH_DELIVERY_DETAILS WDD,

EGP_SYSTEM_ITEMS_VL MSI,

INV_SALES_ORDERS MSO

WHERE WDD.INV_INTERFACED_FLAG <> 'Y'

AND WDD.SOURCE_HEADER_ID = MSO.SALES_ORDER_ID

AND MSI.inventory_item_id = WDD.inventory_item_id

AND WDD.ORGANIZATION_ID = MSI.ORGANIZATION_ID

UNION ALL

/* Work Order Demand*/

SELECT

WO.ORGANIZATION_ID,

WO.COMPONENT_ITEM_ID AS INVENTORY_ITEM_ID,

5 DEMAND_SOURCE_TYPE_ID,

WO.WORK_ORDER_ID AS DEMAND_SOURCE_HEADER_ID,

WO.WO_OPERATION_ID AS DEMAND_SOURCE_LINE_ID,

NULL AS DEMAND_SOURCE_NAME,

NULL AS DEMAND_SOURCE_LINE_NUMBER,

WO.WORK_ORDER_NUMBER AS DEMAND_SOURCE_HEADER_NUMBER,

WO.REQUESTED_QUANTITY AS ORDERED_QUANTITY,

WO.DEMAND_UOM AS ORDERED_UOM_CODE,

(inv_decimals_pub.get_primary_quantity(

WO.ORGANIZATION_ID,

WO.COMPONENT_ITEM_ID,

WO.DEMAND_UOM,

WO.REQUESTED_QUANTITY

)) AS PRIMARY_DEMAND_QUANTITY,

WO.DEMAND_UOM AS PRIMARY_UOM_CODE,

NULL AS CUSTOMER_ID,

WO.REQUIRED_DATE AS DUE_DATE,

WO.DEMAND_PROJECT_ID,

WO.DEMAND_TASK_ID,

WO.ASSEMBLY_ITEM_ID AS ASSEMBLY_ITEM_ID,

WO.OPERATION_SEQ_NUMBER AS OPERATION_SEQ_NUMBER,

NULL AS ORDER_INTERFACE_STATUS

FROM WIE_WO_RESERVABLE_COMPS_V WO