VIEW: APPS.CS_SR_CP_NEW_ACCT_EAM_RG_V

Source

View Text - Preformatted

SELECT ITEM.INSTANCE_ID , SI.CONCATENATED_SEGMENTS PRODUCT_NAME , SI.DESCRIPTION PRODUCT_DESCRIPTION , ITEM.INVENTORY_ITEM_ID , ITEM.SERIAL_NUMBER , ITEM.SYSTEM_ID , DECODE(SIGN(TRUNC(NVL(SYSB.END_DATE_ACTIVE,SYSDATE)) -trunc(SYSDATE)), -1, NULL, SYSL.NAME) SYSTEM_NAME , ITEM.INVENTORY_REVISION , SI.ITEM_TYPE PRODUCT_TYPE , ITEM.INSTANCE_STATUS_ID , INST.NAME CUSTOMER_PRODUCT_STATUS , ITEM.INSTANCE_NUMBER , ITEM.LOT_NUMBER , ASOHDR.CUST_PO_NUMBER , ASOHDR.ORDER_NUMBER , to_number(null) PLATFORM_ID , null PLATFORM , to_number(null) PLATFORM_VERSION_ID , null VERSION , INST.INCIDENT_ALLOWED_FLAG , ITEM.OWNER_PARTY_ACCOUNT_ID PARTY_ACCOUNT_ID , ITEM.LOCATION_ID , ITEM.INV_MASTER_ORGANIZATION_ID , ITEM.EXTERNAL_REFERENCE , ITEM.OWNER_PARTY_ID , ITEM.OWNER_PARTY_SOURCE_TABLE , ITEM.LOCATION_TYPE_CODE , SI.ORGANIZATION_ID , ITEM.INSTANCE_DESCRIPTION INSTANCE_NAME , MP.MAINT_ORGANIZATION_ID FROM CSI_ITEM_INSTANCES ITEM, CSI_INSTANCE_STATUSES INST, CSI_SYSTEMS_TL SYSL, CSI_SYSTEMS_B SYSB, MTL_SYSTEM_ITEMS_VL SI, OE_ORDER_LINES_ALL ASOLIN, OE_ORDER_HEADERS_ALL ASOHDR , MTL_PARAMETERS MP WHERE ITEM.SYSTEM_ID = SYSB.SYSTEM_ID(+) AND SYSL.LANGUAGE(+) = userenv('LANG') AND SYSB.SYSTEM_ID = SYSL.SYSTEM_ID (+) AND ITEM.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND INST.INSTANCE_STATUS_ID = ITEM.INSTANCE_STATUS_ID AND INST.INCIDENT_ALLOWED_FLAG = 'Y' AND ITEM.INVENTORY_ITEM_ID =SI.INVENTORY_ITEM_ID AND ITEM.LAST_OE_ORDER_LINE_ID= ASOLIN.LINE_ID (+) AND ASOLIN.HEADER_ID= ASOHDR.HEADER_ID (+) AND trunc(SYSDATE) BETWEEN NVL(trunc(ITEM.ACTIVE_START_DATE), trunc(SYSDATE)) AND NVL(trunc(ITEM.ACTIVE_END_DATE), trunc(SYSDATE)) AND SI.SERV_REQ_ENABLED_CODE = 'E' AND SI.COMMS_NL_TRACKABLE_FLAG='Y' AND SI.EAM_ITEM_TYPE IN (1,3) AND SI.ORGANIZATION_ID = ITEM.LAST_VLD_ORGANIZATION_ID AND SI.SERIAL_NUMBER_CONTROL_CODE <> 1 AND NVL (ITEM.ACTIVE_START_DATE, SYSDATE-1) <= SYSDATE AND NVL(ITEM.ACTIVE_END_DATE, SYSDATE+1) >= SYSDATE AND SI.ORGANIZATION_ID = MP.ORGANIZATION_ID

View Text - HTML Formatted

SELECT ITEM.INSTANCE_ID
, SI.CONCATENATED_SEGMENTS PRODUCT_NAME
, SI.DESCRIPTION PRODUCT_DESCRIPTION
, ITEM.INVENTORY_ITEM_ID
, ITEM.SERIAL_NUMBER
, ITEM.SYSTEM_ID
, DECODE(SIGN(TRUNC(NVL(SYSB.END_DATE_ACTIVE
, SYSDATE)) -TRUNC(SYSDATE))
, -1
, NULL
, SYSL.NAME) SYSTEM_NAME
, ITEM.INVENTORY_REVISION
, SI.ITEM_TYPE PRODUCT_TYPE
, ITEM.INSTANCE_STATUS_ID
, INST.NAME CUSTOMER_PRODUCT_STATUS
, ITEM.INSTANCE_NUMBER
, ITEM.LOT_NUMBER
, ASOHDR.CUST_PO_NUMBER
, ASOHDR.ORDER_NUMBER
, TO_NUMBER(NULL) PLATFORM_ID
, NULL PLATFORM
, TO_NUMBER(NULL) PLATFORM_VERSION_ID
, NULL VERSION
, INST.INCIDENT_ALLOWED_FLAG
, ITEM.OWNER_PARTY_ACCOUNT_ID PARTY_ACCOUNT_ID
, ITEM.LOCATION_ID
, ITEM.INV_MASTER_ORGANIZATION_ID
, ITEM.EXTERNAL_REFERENCE
, ITEM.OWNER_PARTY_ID
, ITEM.OWNER_PARTY_SOURCE_TABLE
, ITEM.LOCATION_TYPE_CODE
, SI.ORGANIZATION_ID
, ITEM.INSTANCE_DESCRIPTION INSTANCE_NAME
, MP.MAINT_ORGANIZATION_ID
FROM CSI_ITEM_INSTANCES ITEM
, CSI_INSTANCE_STATUSES INST
, CSI_SYSTEMS_TL SYSL
, CSI_SYSTEMS_B SYSB
, MTL_SYSTEM_ITEMS_VL SI
, OE_ORDER_LINES_ALL ASOLIN
, OE_ORDER_HEADERS_ALL ASOHDR
, MTL_PARAMETERS MP
WHERE ITEM.SYSTEM_ID = SYSB.SYSTEM_ID(+)
AND SYSL.LANGUAGE(+) = USERENV('LANG')
AND SYSB.SYSTEM_ID = SYSL.SYSTEM_ID (+)
AND ITEM.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND INST.INSTANCE_STATUS_ID = ITEM.INSTANCE_STATUS_ID
AND INST.INCIDENT_ALLOWED_FLAG = 'Y'
AND ITEM.INVENTORY_ITEM_ID =SI.INVENTORY_ITEM_ID
AND ITEM.LAST_OE_ORDER_LINE_ID= ASOLIN.LINE_ID (+)
AND ASOLIN.HEADER_ID= ASOHDR.HEADER_ID (+)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ITEM.ACTIVE_START_DATE)
, TRUNC(SYSDATE))
AND NVL(TRUNC(ITEM.ACTIVE_END_DATE)
, TRUNC(SYSDATE))
AND SI.SERV_REQ_ENABLED_CODE = 'E'
AND SI.COMMS_NL_TRACKABLE_FLAG='Y'
AND SI.EAM_ITEM_TYPE IN (1
, 3)
AND SI.ORGANIZATION_ID = ITEM.LAST_VLD_ORGANIZATION_ID
AND SI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND NVL (ITEM.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(ITEM.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND SI.ORGANIZATION_ID = MP.ORGANIZATION_ID