VIEW: APPS.MTL_SYSTEM_ITEMS_FKEYS_V

Source

View Text - Preformatted

SELECT MSI.INVENTORY_ITEM_ID , MSI.ORGANIZATION_ID , ORG3.ORGANIZATION_CODE , ORG3.MASTER_ORGANIZATION_ID , DECODE(MSI.ORGANIZATION_ID, ORG3.MASTER_ORGANIZATION_ID, 0, 1) , PER.FULL_NAME , RA1.NAME , RA2.NAME , ORG1.NAME , PO1.DISPLAYED_FIELD , HAZ.HAZARD_CLASS , PUN.UN_NUMBER , PO3.DISPLAYED_FIELD , PO2.DISPLAYED_FIELD , ROUT.ROUTING_NAME , ORG2.NAME , ATP.RULE_NAME , PICK.PICKING_RULE_NAME , TERM.NAME , FND.MEANING , FND2.MEANING , MFG1.MEANING , MFG2.MEANING , MFG3.MEANING , MFG4.MEANING , MFG5.MEANING , PO4.DISPLAYED_FIELD , MFG6.MEANING , MFG7.MEANING , MFG9.MEANING , MFG10.MEANING , MFG11.MEANING , MFG12.MEANING , MFG13.MEANING , MFG14.MEANING , MFG15.MEANING , MFG16.MEANING , MFG17.MEANING , MFG18.MEANING , MFG19.MEANING , MFG20.MEANING , MFG21.MEANING , MFG22.MEANING , MFG23.MEANING , MFG24.MEANING , MSI.ENGINEERING_DATE , COV.NAME , CSL.MEANING , FND1.MEANING , FND3.MEANING , FND4.MEANING FROM MTL_PARAMETERS ORG3, MTL_SYSTEM_ITEMS_B MSI, PER_PEOPLE_F PER, PO_HAZARD_CLASSES HAZ, PO_UN_NUMBERS PUN, MTL_PICKING_RULES PICK, MTL_ATP_RULES ATP, HR_ORGANIZATION_UNITS ORG1, HR_ORGANIZATION_UNITS ORG2, RCV_ROUTING_HEADERS ROUT, RA_RULES RA1, RA_RULES RA2, RA_TERMS TERM, PO_LOOKUP_CODES PO1, PO_LOOKUP_CODES PO2, PO_LOOKUP_CODES PO3, FND_COMMON_LOOKUPS FND, FND_COMMON_LOOKUPS FND2, MFG_LOOKUPS MFG1, MFG_LOOKUPS MFG2, MFG_LOOKUPS MFG3, MFG_LOOKUPS MFG4, MFG_LOOKUPS MFG5, PO_LOOKUP_CODES PO4, MFG_LOOKUPS MFG6, MFG_LOOKUPS MFG7, MFG_LOOKUPS MFG9, MFG_LOOKUPS MFG10, MFG_LOOKUPS MFG11, MFG_LOOKUPS MFG12, MFG_LOOKUPS MFG13, MFG_LOOKUPS MFG14, MFG_LOOKUPS MFG15, MFG_LOOKUPS MFG16, MFG_LOOKUPS MFG17, MFG_LOOKUPS MFG18, MFG_LOOKUPS MFG19, MFG_LOOKUPS MFG20, MFG_LOOKUPS MFG21, MFG_LOOKUPS MFG22, MFG_LOOKUPS MFG23, MFG_LOOKUPS MFG24, OKS_COVERAGE_TEMPLTS_V COV, CS_LOOKUPS CSL, FND_LOOKUPS FND1, FND_COMMON_LOOKUPS FND3, FND_COMMON_LOOKUPS FND4 WHERE PER.PERSON_ID (+) = MSI.BUYER_ID AND PER.EFFECTIVE_START_DATE (+) < SYSDATE AND PER.EFFECTIVE_END_DATE (+) > SYSDATE AND HAZ.HAZARD_CLASS_ID (+) = MSI.HAZARD_CLASS_ID AND PUN.UN_NUMBER_ID (+) = MSI.UN_NUMBER_ID AND PICK.PICKING_RULE_ID (+) = MSI.PICKING_RULE_ID AND ATP.RULE_ID (+) = MSI.ATP_RULE_ID AND ORG1.ORGANIZATION_ID (+) = MSI.DEFAULT_SHIPPING_ORG AND ORG2.ORGANIZATION_ID (+) = MSI.SOURCE_ORGANIZATION_ID AND ORG3.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID AND ROUT.ROUTING_HEADER_ID (+) = MSI.RECEIVING_ROUTING_ID AND RA1.RULE_ID (+) = MSI.ACCOUNTING_RULE_ID AND RA2.RULE_ID (+) = MSI.INVOICING_RULE_ID AND TERM.TERM_ID (+) = MSI.PAYMENT_TERMS_ID AND PO1.LOOKUP_CODE (+) = MSI.QTY_RCV_EXCEPTION_CODE AND PO1.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL' AND PO2.LOOKUP_CODE (+) = MSI.RECEIPT_DAYS_EXCEPTION_CODE AND PO2.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL' AND PO3.LOOKUP_CODE (+) = MSI.ENFORCE_SHIP_TO_LOCATION_CODE AND PO3.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL' AND FND.APPLICATION_ID (+) = 401 AND FND.LOOKUP_CODE (+) = MSI.ITEM_TYPE AND FND.LOOKUP_TYPE (+) = 'ITEM_TYPE' AND FND2.LOOKUP_CODE (+) = MSI.CONTAINER_TYPE_CODE AND FND2.LOOKUP_TYPE (+) = 'CONTAINER_TYPE' AND MFG1.LOOKUP_CODE (+) = MSI.INVENTORY_PLANNING_CODE AND MFG1.LOOKUP_TYPE (+) = 'MTL_MATERIAL_PLANNING' AND MFG2.LOOKUP_CODE (+) = MSI.LOCATION_CONTROL_CODE AND MFG2.LOOKUP_TYPE (+) = 'MTL_LOCATION_CONTROL' AND MFG3.LOOKUP_CODE (+) = MSI.LOT_CONTROL_CODE AND MFG3.LOOKUP_TYPE (+) = 'MTL_LOT_CONTROL' AND MFG4.LOOKUP_CODE (+) = MSI.MRP_PLANNING_CODE AND MFG4.LOOKUP_TYPE (+) = 'MRP_PLANNING_CODE' AND MFG5.LOOKUP_CODE (+) = MSI.MRP_SAFETY_STOCK_CODE AND MFG5.LOOKUP_TYPE (+) = 'MTL_SAFETY_STOCK_TYPE' AND PO4.LOOKUP_CODE (+) = MSI.OUTSIDE_OPERATION_UOM_TYPE AND PO4.LOOKUP_TYPE (+) = 'OUTSIDE OPERATION UOM TYPE' AND MFG6.LOOKUP_CODE (+) = MSI.PLANNING_MAKE_BUY_CODE AND MFG6.LOOKUP_TYPE (+) = 'MTL_PLANNING_MAKE_BUY' AND MFG7.LOOKUP_CODE (+) = MSI.PLANNING_TIME_FENCE_CODE AND MFG7.LOOKUP_TYPE (+) = 'MTL_TIME_FENCE' AND MFG9.LOOKUP_CODE (+) = MSI.RESERVABLE_TYPE AND MFG9.LOOKUP_TYPE (+) = 'MTL_RESERVATION_CONTROL' AND MFG10.LOOKUP_CODE (+) = MSI.RESTRICT_LOCATORS_CODE AND MFG10.LOOKUP_TYPE (+) = 'MTL_LOCATOR_RESTRICTIONS' AND MFG11.LOOKUP_CODE (+) = MSI.RESTRICT_SUBINVENTORIES_CODE AND MFG11.LOOKUP_TYPE (+) = 'MTL_SUBINVENTORY_RESTRICTIONS' AND MFG12.LOOKUP_CODE (+) = MSI.RETURN_INSPECTION_REQUIREMENT AND MFG12.LOOKUP_TYPE (+) = 'MTL_RETURN_INSPECTION' AND MFG13.LOOKUP_CODE (+) = MSI.REVISION_QTY_CONTROL_CODE AND MFG13.LOOKUP_TYPE (+) = 'MTL_ENG_QUANTITY' AND MFG14.LOOKUP_CODE (+) = MSI.ROUNDING_CONTROL_TYPE AND MFG14.LOOKUP_TYPE (+) = 'MTL_ROUNDING' AND MFG15.LOOKUP_CODE (+) = MSI.SERIAL_NUMBER_CONTROL_CODE AND MFG15.LOOKUP_TYPE (+) = 'MTL_SERIAL_NUMBER' AND MFG16.LOOKUP_CODE (+) = MSI.SHELF_LIFE_CODE AND MFG16.LOOKUP_TYPE (+) = 'MTL_SHELF_LIFE' AND MFG17.LOOKUP_CODE (+) = MSI.SOURCE_TYPE AND MFG17.LOOKUP_TYPE (+) = 'MTL_SOURCE_TYPES' AND MFG18.LOOKUP_CODE (+) = MSI.WIP_SUPPLY_TYPE AND MFG18.LOOKUP_TYPE (+) = 'WIP_SUPPLY' AND MFG19.LOOKUP_CODE (+) = MSI.ALLOWED_UNITS_LOOKUP_CODE AND MFG19.LOOKUP_TYPE (+) = 'MTL_CONVERSION_TYPE' AND MFG20.LOOKUP_CODE (+) = MSI.ATO_FORECAST_CONTROL AND MFG20.LOOKUP_TYPE (+) = 'MRP_ATO_FORECAST_CONTROL' AND MFG21.LOOKUP_CODE (+) = MSI.AUTO_REDUCE_MPS AND MFG21.LOOKUP_TYPE (+) = 'MRP_AUTO_REDUCE_MPS' AND MFG22.LOOKUP_CODE (+) = MSI.BOM_ITEM_TYPE AND MFG22.LOOKUP_TYPE (+) = 'BOM_ITEM_TYPE' AND MFG23.LOOKUP_CODE (+) = MSI.DEMAND_TIME_FENCE_CODE AND MFG23.LOOKUP_TYPE (+) = 'MTL_TIME_FENCE' AND MFG24.LOOKUP_CODE (+) = MSI.RELEASE_TIME_FENCE_CODE AND MFG24.LOOKUP_TYPE (+) = 'MTL_RELEASE_TIME_FENCE' AND COV.ID (+) = MSI.COVERAGE_SCHEDULE_ID AND CSL.LOOKUP_CODE (+) = MSI.MATERIAL_BILLABLE_FLAG AND CSL.LOOKUP_TYPE (+) = 'MTL_SERVICE_BILLABLE_FLAG' AND FND1.LOOKUP_CODE (+) = MSI.END_ASSEMBLY_PEGGING_FLAG AND FND1.LOOKUP_TYPE (+) = 'ASSEMBLY_PEGGING_CODE' AND FND3.APPLICATION_ID (+) = 401 AND FND3.LOOKUP_TYPE (+) = 'ATP_FLAG' AND FND3.LOOKUP_CODE (+) = MSI.ATP_FLAG AND FND4.APPLICATION_ID (+) = 401 AND FND4.LOOKUP_TYPE (+) = 'ATP_FLAG' AND FND4.LOOKUP_CODE (+) = MSI.ATP_COMPONENTS_FLAG

View Text - HTML Formatted

SELECT MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, ORG3.ORGANIZATION_CODE
, ORG3.MASTER_ORGANIZATION_ID
, DECODE(MSI.ORGANIZATION_ID
, ORG3.MASTER_ORGANIZATION_ID
, 0
, 1)
, PER.FULL_NAME
, RA1.NAME
, RA2.NAME
, ORG1.NAME
, PO1.DISPLAYED_FIELD
, HAZ.HAZARD_CLASS
, PUN.UN_NUMBER
, PO3.DISPLAYED_FIELD
, PO2.DISPLAYED_FIELD
, ROUT.ROUTING_NAME
, ORG2.NAME
, ATP.RULE_NAME
, PICK.PICKING_RULE_NAME
, TERM.NAME
, FND.MEANING
, FND2.MEANING
, MFG1.MEANING
, MFG2.MEANING
, MFG3.MEANING
, MFG4.MEANING
, MFG5.MEANING
, PO4.DISPLAYED_FIELD
, MFG6.MEANING
, MFG7.MEANING
, MFG9.MEANING
, MFG10.MEANING
, MFG11.MEANING
, MFG12.MEANING
, MFG13.MEANING
, MFG14.MEANING
, MFG15.MEANING
, MFG16.MEANING
, MFG17.MEANING
, MFG18.MEANING
, MFG19.MEANING
, MFG20.MEANING
, MFG21.MEANING
, MFG22.MEANING
, MFG23.MEANING
, MFG24.MEANING
, MSI.ENGINEERING_DATE
, COV.NAME
, CSL.MEANING
, FND1.MEANING
, FND3.MEANING
, FND4.MEANING
FROM MTL_PARAMETERS ORG3
, MTL_SYSTEM_ITEMS_B MSI
, PER_PEOPLE_F PER
, PO_HAZARD_CLASSES HAZ
, PO_UN_NUMBERS PUN
, MTL_PICKING_RULES PICK
, MTL_ATP_RULES ATP
, HR_ORGANIZATION_UNITS ORG1
, HR_ORGANIZATION_UNITS ORG2
, RCV_ROUTING_HEADERS ROUT
, RA_RULES RA1
, RA_RULES RA2
, RA_TERMS TERM
, PO_LOOKUP_CODES PO1
, PO_LOOKUP_CODES PO2
, PO_LOOKUP_CODES PO3
, FND_COMMON_LOOKUPS FND
, FND_COMMON_LOOKUPS FND2
, MFG_LOOKUPS MFG1
, MFG_LOOKUPS MFG2
, MFG_LOOKUPS MFG3
, MFG_LOOKUPS MFG4
, MFG_LOOKUPS MFG5
, PO_LOOKUP_CODES PO4
, MFG_LOOKUPS MFG6
, MFG_LOOKUPS MFG7
, MFG_LOOKUPS MFG9
, MFG_LOOKUPS MFG10
, MFG_LOOKUPS MFG11
, MFG_LOOKUPS MFG12
, MFG_LOOKUPS MFG13
, MFG_LOOKUPS MFG14
, MFG_LOOKUPS MFG15
, MFG_LOOKUPS MFG16
, MFG_LOOKUPS MFG17
, MFG_LOOKUPS MFG18
, MFG_LOOKUPS MFG19
, MFG_LOOKUPS MFG20
, MFG_LOOKUPS MFG21
, MFG_LOOKUPS MFG22
, MFG_LOOKUPS MFG23
, MFG_LOOKUPS MFG24
, OKS_COVERAGE_TEMPLTS_V COV
, CS_LOOKUPS CSL
, FND_LOOKUPS FND1
, FND_COMMON_LOOKUPS FND3
, FND_COMMON_LOOKUPS FND4
WHERE PER.PERSON_ID (+) = MSI.BUYER_ID
AND PER.EFFECTIVE_START_DATE (+) < SYSDATE
AND PER.EFFECTIVE_END_DATE (+) > SYSDATE
AND HAZ.HAZARD_CLASS_ID (+) = MSI.HAZARD_CLASS_ID
AND PUN.UN_NUMBER_ID (+) = MSI.UN_NUMBER_ID
AND PICK.PICKING_RULE_ID (+) = MSI.PICKING_RULE_ID
AND ATP.RULE_ID (+) = MSI.ATP_RULE_ID
AND ORG1.ORGANIZATION_ID (+) = MSI.DEFAULT_SHIPPING_ORG
AND ORG2.ORGANIZATION_ID (+) = MSI.SOURCE_ORGANIZATION_ID
AND ORG3.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND ROUT.ROUTING_HEADER_ID (+) = MSI.RECEIVING_ROUTING_ID
AND RA1.RULE_ID (+) = MSI.ACCOUNTING_RULE_ID
AND RA2.RULE_ID (+) = MSI.INVOICING_RULE_ID
AND TERM.TERM_ID (+) = MSI.PAYMENT_TERMS_ID
AND PO1.LOOKUP_CODE (+) = MSI.QTY_RCV_EXCEPTION_CODE
AND PO1.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL'
AND PO2.LOOKUP_CODE (+) = MSI.RECEIPT_DAYS_EXCEPTION_CODE
AND PO2.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL'
AND PO3.LOOKUP_CODE (+) = MSI.ENFORCE_SHIP_TO_LOCATION_CODE
AND PO3.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL'
AND FND.APPLICATION_ID (+) = 401
AND FND.LOOKUP_CODE (+) = MSI.ITEM_TYPE
AND FND.LOOKUP_TYPE (+) = 'ITEM_TYPE'
AND FND2.LOOKUP_CODE (+) = MSI.CONTAINER_TYPE_CODE
AND FND2.LOOKUP_TYPE (+) = 'CONTAINER_TYPE'
AND MFG1.LOOKUP_CODE (+) = MSI.INVENTORY_PLANNING_CODE
AND MFG1.LOOKUP_TYPE (+) = 'MTL_MATERIAL_PLANNING'
AND MFG2.LOOKUP_CODE (+) = MSI.LOCATION_CONTROL_CODE
AND MFG2.LOOKUP_TYPE (+) = 'MTL_LOCATION_CONTROL'
AND MFG3.LOOKUP_CODE (+) = MSI.LOT_CONTROL_CODE
AND MFG3.LOOKUP_TYPE (+) = 'MTL_LOT_CONTROL'
AND MFG4.LOOKUP_CODE (+) = MSI.MRP_PLANNING_CODE
AND MFG4.LOOKUP_TYPE (+) = 'MRP_PLANNING_CODE'
AND MFG5.LOOKUP_CODE (+) = MSI.MRP_SAFETY_STOCK_CODE
AND MFG5.LOOKUP_TYPE (+) = 'MTL_SAFETY_STOCK_TYPE'
AND PO4.LOOKUP_CODE (+) = MSI.OUTSIDE_OPERATION_UOM_TYPE
AND PO4.LOOKUP_TYPE (+) = 'OUTSIDE OPERATION UOM TYPE'
AND MFG6.LOOKUP_CODE (+) = MSI.PLANNING_MAKE_BUY_CODE
AND MFG6.LOOKUP_TYPE (+) = 'MTL_PLANNING_MAKE_BUY'
AND MFG7.LOOKUP_CODE (+) = MSI.PLANNING_TIME_FENCE_CODE
AND MFG7.LOOKUP_TYPE (+) = 'MTL_TIME_FENCE'
AND MFG9.LOOKUP_CODE (+) = MSI.RESERVABLE_TYPE
AND MFG9.LOOKUP_TYPE (+) = 'MTL_RESERVATION_CONTROL'
AND MFG10.LOOKUP_CODE (+) = MSI.RESTRICT_LOCATORS_CODE
AND MFG10.LOOKUP_TYPE (+) = 'MTL_LOCATOR_RESTRICTIONS'
AND MFG11.LOOKUP_CODE (+) = MSI.RESTRICT_SUBINVENTORIES_CODE
AND MFG11.LOOKUP_TYPE (+) = 'MTL_SUBINVENTORY_RESTRICTIONS'
AND MFG12.LOOKUP_CODE (+) = MSI.RETURN_INSPECTION_REQUIREMENT
AND MFG12.LOOKUP_TYPE (+) = 'MTL_RETURN_INSPECTION'
AND MFG13.LOOKUP_CODE (+) = MSI.REVISION_QTY_CONTROL_CODE
AND MFG13.LOOKUP_TYPE (+) = 'MTL_ENG_QUANTITY'
AND MFG14.LOOKUP_CODE (+) = MSI.ROUNDING_CONTROL_TYPE
AND MFG14.LOOKUP_TYPE (+) = 'MTL_ROUNDING'
AND MFG15.LOOKUP_CODE (+) = MSI.SERIAL_NUMBER_CONTROL_CODE
AND MFG15.LOOKUP_TYPE (+) = 'MTL_SERIAL_NUMBER'
AND MFG16.LOOKUP_CODE (+) = MSI.SHELF_LIFE_CODE
AND MFG16.LOOKUP_TYPE (+) = 'MTL_SHELF_LIFE'
AND MFG17.LOOKUP_CODE (+) = MSI.SOURCE_TYPE
AND MFG17.LOOKUP_TYPE (+) = 'MTL_SOURCE_TYPES'
AND MFG18.LOOKUP_CODE (+) = MSI.WIP_SUPPLY_TYPE
AND MFG18.LOOKUP_TYPE (+) = 'WIP_SUPPLY'
AND MFG19.LOOKUP_CODE (+) = MSI.ALLOWED_UNITS_LOOKUP_CODE
AND MFG19.LOOKUP_TYPE (+) = 'MTL_CONVERSION_TYPE'
AND MFG20.LOOKUP_CODE (+) = MSI.ATO_FORECAST_CONTROL
AND MFG20.LOOKUP_TYPE (+) = 'MRP_ATO_FORECAST_CONTROL'
AND MFG21.LOOKUP_CODE (+) = MSI.AUTO_REDUCE_MPS
AND MFG21.LOOKUP_TYPE (+) = 'MRP_AUTO_REDUCE_MPS'
AND MFG22.LOOKUP_CODE (+) = MSI.BOM_ITEM_TYPE
AND MFG22.LOOKUP_TYPE (+) = 'BOM_ITEM_TYPE'
AND MFG23.LOOKUP_CODE (+) = MSI.DEMAND_TIME_FENCE_CODE
AND MFG23.LOOKUP_TYPE (+) = 'MTL_TIME_FENCE'
AND MFG24.LOOKUP_CODE (+) = MSI.RELEASE_TIME_FENCE_CODE
AND MFG24.LOOKUP_TYPE (+) = 'MTL_RELEASE_TIME_FENCE'
AND COV.ID (+) = MSI.COVERAGE_SCHEDULE_ID
AND CSL.LOOKUP_CODE (+) = MSI.MATERIAL_BILLABLE_FLAG
AND CSL.LOOKUP_TYPE (+) = 'MTL_SERVICE_BILLABLE_FLAG'
AND FND1.LOOKUP_CODE (+) = MSI.END_ASSEMBLY_PEGGING_FLAG
AND FND1.LOOKUP_TYPE (+) = 'ASSEMBLY_PEGGING_CODE'
AND FND3.APPLICATION_ID (+) = 401
AND FND3.LOOKUP_TYPE (+) = 'ATP_FLAG'
AND FND3.LOOKUP_CODE (+) = MSI.ATP_FLAG
AND FND4.APPLICATION_ID (+) = 401
AND FND4.LOOKUP_TYPE (+) = 'ATP_FLAG'
AND FND4.LOOKUP_CODE (+) = MSI.ATP_COMPONENTS_FLAG