INV_SUPPLY_V

Details

• Schema: FUSION

• Object owner: INV

• Object type: VIEW

Columns

Name

SUPPLY_TYPE_CODE SUPPLY_SOURCE_ID LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN CREATED_BY CREATION_DATE REQUEST_ID REQ_HEADER_ID REQ_LINE_ID PO_HEADER_ID PO_LINE_ID PO_LINE_LOCATION_ID PO_DISTRIBUTION_ID SHIPMENT_HEADER_ID SHIPMENT_LINE_ID RCV_TRANSACTION_ID ITEM_ID ITEM_REVISION CATEGORY_ID QUANTITY UOM_CODE TO_ORG_PRIMARY_QUANTITY TO_ORG_PRIMARY_UOM_CODE RECEIPT_DATE NEED_BY_DATE EXPECTED_DELIVERY_DATE DESTINATION_TYPE_CODE LOCATION_ID FROM_ORGANIZATION_ID FROM_SUBINVENTORY TO_ORGANIZATION_ID TO_SUBINVENTORY INTRANSIT_OWNING_ORG_ID MRP_PRIMARY_QUANTITY MRP_PRIMARY_UOM MRP_EXPECTED_DELIVERY_DATE MRP_DESTINATION_TYPE_CODE MRP_TO_ORGANIZATION_ID MRP_TO_SUBINVENTORY CHANGE_FLAG CHANGE_TYPE COST_GROUP_ID JOB_DEFINITION_NAME JOB_DEFINITION_PACKAGE OBJECT_VERSION_NUMBER TRANSFER_ORDER_HEADER_ID TRANSFER_ORDER_LINE_ID PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_CODE

Query

SQL_Statement

with INL_PRJ_IDS_REF as ( ( select PO_DISTRIBUTIONS_ALL.PJC_PROJECT_ID as PROJECT_ID, PO_DISTRIBUTIONS_ALL.PJC_TASK_ID as TASK_ID, PO_LINE_LOCATIONS_ALL.country_of_origin_code as COUNTRY_OF_ORIGIN_CODE, PO_LINE_LOCATIONS_ALL.po_line_id as LINE_ID, PO_LINE_LOCATIONS_ALL.po_header_id as HEADER_ID, 'PO SET' as GROUP_ID, PO_DISTRIBUTIONS_ALL.line_location_id as PO_LINE_LOCATION_ID from PO_DISTRIBUTIONS_ALL, PO_LINE_LOCATIONS_ALL where PO_DISTRIBUTIONS_ALL.line_location_id=po_line_locations_all.line_location_id ) union all ( select PJC_PROJECT_ID as PROJECT_ID, PJC_TASK_ID as TASK_ID, null as country_of_origin_code, REQUISITION_LINE_ID as LINE_ID, REQUISITION_LINE_ID as HEADER_ID, 'REQ SET' as GROUP_ID, null as PO_LINE_LOCATION_ID from por_req_distributions_all ) union all ( select PJC_PROJECT_ID as PROJECT_ID, PJC_TASK_ID as TASK_ID, null as COUNTRY_OF_ORIGIN_CODE, inv_transfer_order_distribs.LINE_ID as LINE_ID, inv_transfer_order_distribs.LINE_ID as HEADER_ID, 'TO SET' as GROUP_ID, null as PO_LINE_LOCATION_ID from inv_transfer_order_distribs ) union all ( select PROJECT_ID, TASK_ID, COUNTRY_OF_ORIGIN_CODE, SHIPMENT_LINE_ID AS LINE_ID, SHIPMENT_HEADER_ID AS HEADER_ID, 'SHIPMENT SET' AS GROUP_ID, NULL AS PO_LINE_LOCATION_ID from rcv_shipment_lines ) ), INL_SUP_CLASSIFIED as ( select rowid as ident, SUP.PO_LINE_LOCATION_ID as PO_LINE_LOCATION_ID, (case when (SUP.supply_type_code in ( 'PO', 'RECEIVING', 'SHIPMENT' ) and SUP.PO_HEADER_ID is not null and SUP.PO_LINE_ID is not null) then SUP.PO_HEADER_ID when SUP.supply_type_code in ( 'TRANSFER_ORDER', 'RECEIVING', 'SHIPMENT' ) and SUP.TRANSFER_ORDER_HEADER_ID is not null and SUP.TRANSFER_ORDER_LINE_ID is not null then SUP.TRANSFER_ORDER_LINE_ID when SUP.supply_type_code = 'REQ' and SUP.REQ_HEADER_ID is not null and SUP.REQ_LINE_ID is not null then SUP.REQ_LINE_ID when SUP.supply_type_code = 'SHIPMENT' and SUP.SHIPMENT_HEADER_ID is not null and SUP.SHIPMENT_LINE_ID is not null then SUP.SHIPMENT_HEADER_ID end) as header_id, (case when (SUP.supply_type_code in ( 'PO', 'RECEIVING', 'SHIPMENT' ) and SUP.PO_HEADER_ID is not null and SUP.PO_LINE_ID is not null) then SUP.PO_LINE_ID when SUP.supply_type_code in ( 'TRANSFER_ORDER', 'RECEIVING', 'SHIPMENT' ) and SUP.TRANSFER_ORDER_HEADER_ID is not null and SUP.TRANSFER_ORDER_LINE_ID is not null then SUP.TRANSFER_ORDER_LINE_ID when SUP.supply_type_code = 'REQ' and SUP.REQ_HEADER_ID is not null and SUP.REQ_LINE_ID is not null then SUP.REQ_LINE_ID when SUP.supply_type_code = 'SHIPMENT' and SUP.SHIPMENT_HEADER_ID is not null and SUP.SHIPMENT_LINE_ID is not null then SUP.SHIPMENT_LINE_ID end) as line_id, (case when (SUP.supply_type_code in ( 'PO', 'RECEIVING', 'SHIPMENT' ) and SUP.PO_HEADER_ID is not null and SUP.PO_LINE_ID is not null) then 'PO SET' when SUP.supply_type_code in ( 'TRANSFER_ORDER', 'RECEIVING', 'SHIPMENT' ) and SUP.TRANSFER_ORDER_HEADER_ID is not null and SUP.TRANSFER_ORDER_LINE_ID is not null then 'TO SET' when SUP.supply_type_code = 'REQ' and SUP.REQ_HEADER_ID is not null and SUP.REQ_LINE_ID is not null then 'REQ SET' when SUP.supply_type_code = 'SHIPMENT' and SUP.SHIPMENT_HEADER_ID is not null and SUP.SHIPMENT_LINE_ID is not null then 'SHIPMENT SET' end) as GROUP_ID from inv_supply sup ) ,INL_SUP_CLASSIFIED_DIST as ( select distinct SUPS.ident as ident, REFS.header_id, REFS.line_id, REFS.group_id, REFS.PROJECT_ID, REFS.TASK_ID, REFS.COUNTRY_OF_ORIGIN_CODE from INL_SUP_CLASSIFIED SUPS, INL_PRJ_IDS_REF REFS where SUPS.header_id = REFS.header_id(+) and SUPS.line_id = REFS.line_id(+) and SUPS.group_id = REFS.group_id(+) and (SUPS.GROUP_ID != 'PO SET' or (SUPS.GROUP_ID = 'PO SET' and SUPS.PO_LINE_LOCATION_ID = REFS.PO_LINE_LOCATION_ID)) ) select SUP.SUPPLY_TYPE_CODE ,SUP.SUPPLY_SOURCE_ID ,SUP.LAST_UPDATED_BY ,SUP.LAST_UPDATE_DATE ,SUP.LAST_UPDATE_LOGIN ,SUP.CREATED_BY ,SUP.CREATION_DATE ,SUP.REQUEST_ID ,SUP.REQ_HEADER_ID ,SUP.REQ_LINE_ID ,SUP.PO_HEADER_ID ,SUP.PO_LINE_ID ,SUP.PO_LINE_LOCATION_ID ,SUP.PO_DISTRIBUTION_ID ,SUP.SHIPMENT_HEADER_ID ,SUP.SHIPMENT_LINE_ID ,SUP.RCV_TRANSACTION_ID ,SUP.ITEM_ID ,SUP.ITEM_REVISION ,SUP.CATEGORY_ID ,SUP.QUANTITY ,SUP.UOM_CODE ,SUP.TO_ORG_PRIMARY_QUANTITY ,SUP.TO_ORG_PRIMARY_UOM_CODE ,SUP.RECEIPT_DATE ,SUP.NEED_BY_DATE ,SUP.EXPECTED_DELIVERY_DATE ,SUP.DESTINATION_TYPE_CODE ,SUP.LOCATION_ID ,SUP.FROM_ORGANIZATION_ID ,SUP.FROM_SUBINVENTORY ,SUP.TO_ORGANIZATION_ID ,SUP.TO_SUBINVENTORY ,SUP.INTRANSIT_OWNING_ORG_ID ,SUP.MRP_PRIMARY_QUANTITY ,SUP.MRP_PRIMARY_UOM ,SUP.MRP_EXPECTED_DELIVERY_DATE ,SUP.MRP_DESTINATION_TYPE_CODE ,SUP.MRP_TO_ORGANIZATION_ID ,SUP.MRP_TO_SUBINVENTORY ,SUP.CHANGE_FLAG ,SUP.CHANGE_TYPE ,SUP.COST_GROUP_ID ,SUP.JOB_DEFINITION_NAME ,SUP.JOB_DEFINITION_PACKAGE ,SUP.OBJECT_VERSION_NUMBER ,SUP.TRANSFER_ORDER_HEADER_ID ,SUP.TRANSFER_ORDER_LINE_ID ,SUP_CLASS.PROJECT_ID ,SUP_CLASS.TASK_ID ,SUP_CLASS.COUNTRY_OF_ORIGIN_CODE from INV_SUPPLY SUP, INL_SUP_CLASSIFIED_DIST SUP_CLASS where SUP_CLASS.ident = sup.rowid