VIEW: APPS.AMS_DS_INSTALL_BASE_V

Source

View Text - Preformatted

SELECT CII.INSTANCE_ID INSTANCE_ID, CII.INSTANCE_NUMBER INSTANCE_NUMBER, CII.INSTANCE_STATUS_ID INSTANCE_STATUS_ID , CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, CII.INV_MASTER_ORGANIZATION_ID INV_MASTER_ORGANIZATION_ID, CII.QUANTITY QUANTITY, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE, CII.ACTIVE_START_DATE ACTIVE_START_DATE, CII.ACTIVE_END_DATE ACTIVE_END_DATE, CII.LOCATION_TYPE_CODE LOCATION_TYPE_CODE, CII.LOCATION_ID LOCATION_ID, CII.INV_ORGANIZATION_ID INV_ORGANIZATION_ID, CII.LAST_OE_ORDER_LINE_ID LAST_OE_ORDER_LINE_ID, CII.INSTALL_DATE INSTALL_DATE, CII.MANUALLY_CREATED_FLAG MANUALLY_CREATED_FLAG, CII.RETURN_BY_DATE RETURN_BY_DATE, CII.ACTUAL_RETURN_DATE ACTUAL_RETURN_DATE, CII.CREATION_COMPLETE_FLAG CREATION_COMPLETE_FLAG, CII.COMPLETENESS_FLAG COMPLETENESS_FLAG, CII.CONTEXT CONTEXT, CII.ATTRIBUTE1 ATTRIBUTE1, CII.ATTRIBUTE2 ATTRIBUTE2, CII.ATTRIBUTE3 ATTRIBUTE3, CII.ATTRIBUTE4 ATTRIBUTE4, CII.ATTRIBUTE5 ATTRIBUTE5, CII.ATTRIBUTE6 ATTRIBUTE6, CII.ATTRIBUTE7 ATTRIBUTE7, CII.ATTRIBUTE8 ATTRIBUTE8, CII.ATTRIBUTE9 ATTRIBUTE9, CII.ATTRIBUTE10 ATTRIBUTE10, CII.ATTRIBUTE11 ATTRIBUTE11, CII.ATTRIBUTE12 ATTRIBUTE12, CII.ATTRIBUTE13 ATTRIBUTE13, CII.ATTRIBUTE14 ATTRIBUTE14, CII.ATTRIBUTE15 ATTRIBUTE15, CII.CREATED_BY CREATED_BY, CII.CREATION_DATE CREATION_DATE, CII.INSTANCE_DESCRIPTION INSTANCE_DESCRIPTION, MSI.DESCRIPTION DESCRIPTION, MSI.ITEM_TYPE ITEM_TYPE, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, MSI.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS, MSI.PADDED_CONCATENATED_SEGMENTS PADDED_CONCATENATED_SEGMENTS, LOOH.HEADER_ID LAST_HEADER_ID, LOOH.ORDER_NUMBER LAST_ORDER_NUMBER, LOOH.ORDERED_DATE LAST_ORDERED_DATE, LOOL.LINE_ID LAST_LINE_ID, LOOL.LINE_NUMBER LAST_LINE_NUMBER, LOOL.CUST_PO_NUMBER LAST_CUST_PO_NUMBER, NVL(LOOL.ACTUAL_SHIPMENT_DATE, LOOL.FULFILLMENT_DATE) SHIPPED_DATE, CL1.MEANING INSTANCE_USAGE_MEANING, MSI.DESCRIPTION ITEM_DESCRIPTION, CIS.name , CIS.TERMINATED_FLAG, CII.OWNER_PARTY_ID, CII.OWNER_PARTY_ACCOUNT_ID, hl.CITY, hl.POSTAL_CODE, hl.STATE, hl.PROVINCE, hl.COUNTY, hl.country, MIC.CATEGORY_ID FROM CSI_ITEM_INSTANCES CII, CSI_INSTANCE_STATUSES cis, MTL_SYSTEM_ITEMS_KFV MSI, OE_ORDER_LINES_ALL LOOL, OE_ORDER_HEADERS_ALL LOOH, CSI_LOOKUPS CL1, hz_locations hl, MTL_DEFAULT_CATEGORY_SETS D , MTL_ITEM_CATEGORIES MIC WHERE CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND CII.INV_MASTER_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) AND CII.LAST_OE_ORDER_LINE_ID = LOOL.LINE_ID (+) AND CII.INSTANCE_USAGE_CODE = CL1.LOOKUP_CODE (+) AND LOOL.HEADER_ID = LOOH.HEADER_ID (+) AND CL1.LOOKUP_TYPE (+) = 'CSI_INSTANCE_USAGE_CODE' AND cis.INSTANCE_STATUS_ID = CII.INSTANCE_STATUS_ID and hl.location_id(+) = cii.Location_id and CII.LOCATION_TYPE_CODE IN( 'HZ_LOCATIONS','HZ_PARTY_SITES') and D.FUNCTIONAL_AREA_ID = 11 and MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID and MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID and MIC.CATEGORY_SET_ID = D.CATEGORY_SET_ID

View Text - HTML Formatted

SELECT CII.INSTANCE_ID INSTANCE_ID
, CII.INSTANCE_NUMBER INSTANCE_NUMBER
, CII.INSTANCE_STATUS_ID INSTANCE_STATUS_ID
, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CII.INV_MASTER_ORGANIZATION_ID INV_MASTER_ORGANIZATION_ID
, CII.QUANTITY QUANTITY
, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE
, CII.ACTIVE_START_DATE ACTIVE_START_DATE
, CII.ACTIVE_END_DATE ACTIVE_END_DATE
, CII.LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, CII.LOCATION_ID LOCATION_ID
, CII.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
, CII.LAST_OE_ORDER_LINE_ID LAST_OE_ORDER_LINE_ID
, CII.INSTALL_DATE INSTALL_DATE
, CII.MANUALLY_CREATED_FLAG MANUALLY_CREATED_FLAG
, CII.RETURN_BY_DATE RETURN_BY_DATE
, CII.ACTUAL_RETURN_DATE ACTUAL_RETURN_DATE
, CII.CREATION_COMPLETE_FLAG CREATION_COMPLETE_FLAG
, CII.COMPLETENESS_FLAG COMPLETENESS_FLAG
, CII.CONTEXT CONTEXT
, CII.ATTRIBUTE1 ATTRIBUTE1
, CII.ATTRIBUTE2 ATTRIBUTE2
, CII.ATTRIBUTE3 ATTRIBUTE3
, CII.ATTRIBUTE4 ATTRIBUTE4
, CII.ATTRIBUTE5 ATTRIBUTE5
, CII.ATTRIBUTE6 ATTRIBUTE6
, CII.ATTRIBUTE7 ATTRIBUTE7
, CII.ATTRIBUTE8 ATTRIBUTE8
, CII.ATTRIBUTE9 ATTRIBUTE9
, CII.ATTRIBUTE10 ATTRIBUTE10
, CII.ATTRIBUTE11 ATTRIBUTE11
, CII.ATTRIBUTE12 ATTRIBUTE12
, CII.ATTRIBUTE13 ATTRIBUTE13
, CII.ATTRIBUTE14 ATTRIBUTE14
, CII.ATTRIBUTE15 ATTRIBUTE15
, CII.CREATED_BY CREATED_BY
, CII.CREATION_DATE CREATION_DATE
, CII.INSTANCE_DESCRIPTION INSTANCE_DESCRIPTION
, MSI.DESCRIPTION DESCRIPTION
, MSI.ITEM_TYPE ITEM_TYPE
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, MSI.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, MSI.PADDED_CONCATENATED_SEGMENTS PADDED_CONCATENATED_SEGMENTS
, LOOH.HEADER_ID LAST_HEADER_ID
, LOOH.ORDER_NUMBER LAST_ORDER_NUMBER
, LOOH.ORDERED_DATE LAST_ORDERED_DATE
, LOOL.LINE_ID LAST_LINE_ID
, LOOL.LINE_NUMBER LAST_LINE_NUMBER
, LOOL.CUST_PO_NUMBER LAST_CUST_PO_NUMBER
, NVL(LOOL.ACTUAL_SHIPMENT_DATE
, LOOL.FULFILLMENT_DATE) SHIPPED_DATE
, CL1.MEANING INSTANCE_USAGE_MEANING
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CIS.NAME
, CIS.TERMINATED_FLAG
, CII.OWNER_PARTY_ID
, CII.OWNER_PARTY_ACCOUNT_ID
, HL.CITY
, HL.POSTAL_CODE
, HL.STATE
, HL.PROVINCE
, HL.COUNTY
, HL.COUNTRY
, MIC.CATEGORY_ID
FROM CSI_ITEM_INSTANCES CII
, CSI_INSTANCE_STATUSES CIS
, MTL_SYSTEM_ITEMS_KFV MSI
, OE_ORDER_LINES_ALL LOOL
, OE_ORDER_HEADERS_ALL LOOH
, CSI_LOOKUPS CL1
, HZ_LOCATIONS HL
, MTL_DEFAULT_CATEGORY_SETS D
, MTL_ITEM_CATEGORIES MIC
WHERE CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND CII.INV_MASTER_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND CII.LAST_OE_ORDER_LINE_ID = LOOL.LINE_ID (+)
AND CII.INSTANCE_USAGE_CODE = CL1.LOOKUP_CODE (+)
AND LOOL.HEADER_ID = LOOH.HEADER_ID (+)
AND CL1.LOOKUP_TYPE (+) = 'CSI_INSTANCE_USAGE_CODE'
AND CIS.INSTANCE_STATUS_ID = CII.INSTANCE_STATUS_ID
AND HL.LOCATION_ID(+) = CII.LOCATION_ID
AND CII.LOCATION_TYPE_CODE IN( 'HZ_LOCATIONS'
, 'HZ_PARTY_SITES')
AND D.FUNCTIONAL_AREA_ID = 11
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.CATEGORY_SET_ID = D.CATEGORY_SET_ID