VIEW: APPS.CS_SR_FIND_PROD_MAINT_P_EAM_V

Source

View Text - Preformatted

SELECT HZP.PARTY_NAME PRINCIPAL_PARTY_NAME, ITEM.INSTANCE_ID CUSTOMER_PRODUCT_ID, HZP.PARTY_ID CUSTOMER_ID, ITEM.INVENTORY_ITEM_ID, 'Y' MOST_RECENT_FLAG , ITEM.SERIAL_NUMBER CURRENT_SERIAL_NUMBER, ITEM.INSTANCE_TYPE_CODE TYPE_CODE, ITEM.SYSTEM_ID , DECODE(SIGN(TRUNC(NVL(SYSB.END_DATE_ACTIVE,SYSDATE)) - trunc(SYSDATE)), -1, NULL, SYSL.NAME) SYSTEM_NAME, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER, null INSTALL_SITE_USE_ID, 99999 PARENT_CP_ID, ITEM.INSTANCE_STATUS_ID CUSTOMER_PRODUCT_STATUS_ID, ITEM.INSTANCE_NUMBER REFERENCE_NUMBER, OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUM , ITEM.LOT_NUMBER CP_LOT_NUMBER, ITEM.INVENTORY_REVISION REVISION, ITEM.INSTANCE_DESCRIPTION INSTANCE_NAME, SI.DESCRIPTION PRODUCT_DESCRIPTION , 'N' SERVICED_STATUS_FLAG, OOL.LINE_NUMBER ORIGINAL_LINE_NUMBER , OOL.ACTUAL_SHIPMENT_DATE SHIPPED_DATE, ITEM.QUANTITY, ITEM.EXTERNAL_REFERENCE, ITEM.LOCATION_TYPE_CODE, ITEM.OWNER_PARTY_ACCOUNT_ID ACCOUNT_ID, hza.account_number account_number, SI.ORGANIZATION_ID, ITEM.LOCATION_ID, SI.CONCATENATED_SEGMENTS PRODUCT_NAME, MP.MAINT_ORGANIZATION_ID, HZP.PARTY_NUMBER CUSTOMER_NUMBER FROM HZ_PARTIES HZP, hz_cust_accounts hza, MTL_SYSTEM_ITEMS_VL SI, OE_ORDER_HEADERS_ALL OOH, OE_ORDER_LINES_ALL OOL, CSI_ITEM_INSTANCES ITEM, CSI_SYSTEMS_TL SYSL, CSI_SYSTEMS_B SYSB, CSI_INSTANCE_STATUSES INST , MTL_PARAMETERS MP WHERE ITEM.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID AND ITEM.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND ITEM.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID AND OOL.HEADER_ID = OOH.HEADER_ID AND ITEM.SYSTEM_ID = SYSB.SYSTEM_ID(+) AND SYSL.LANGUAGE(+) = userenv('LANG') AND SYSB.SYSTEM_ID = SYSL.SYSTEM_ID (+) AND ITEM.OWNER_PARTY_ID = HZP.PARTY_ID AND INST.INSTANCE_STATUS_ID = ITEM.INSTANCE_STATUS_ID AND INST.INCIDENT_ALLOWED_FLAG ='Y' AND SI.SERV_REQ_ENABLED_CODE = 'E' AND SI.COMMS_NL_TRACKABLE_FLAG='Y' AND TRUNC(SYSDATE) BETWEEN NVL(trunc(ITEM.ACTIVE_START_DATE), trunc(SYSDATE)) AND NVL(trunc(ITEM.ACTIVE_END_DATE), trunc(SYSDATE)) 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 SI.ORGANIZATION_ID = MP.ORGANIZATION_ID and item.OWNER_PARTY_ACCOUNT_ID = hza.cust_account_id(+)

View Text - HTML Formatted

SELECT HZP.PARTY_NAME PRINCIPAL_PARTY_NAME
, ITEM.INSTANCE_ID CUSTOMER_PRODUCT_ID
, HZP.PARTY_ID CUSTOMER_ID
, ITEM.INVENTORY_ITEM_ID
, 'Y' MOST_RECENT_FLAG
, ITEM.SERIAL_NUMBER CURRENT_SERIAL_NUMBER
, ITEM.INSTANCE_TYPE_CODE TYPE_CODE
, ITEM.SYSTEM_ID
, DECODE(SIGN(TRUNC(NVL(SYSB.END_DATE_ACTIVE
, SYSDATE)) - TRUNC(SYSDATE))
, -1
, NULL
, SYSL.NAME) SYSTEM_NAME
, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE
, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER
, NULL INSTALL_SITE_USE_ID
, 99999 PARENT_CP_ID
, ITEM.INSTANCE_STATUS_ID CUSTOMER_PRODUCT_STATUS_ID
, ITEM.INSTANCE_NUMBER REFERENCE_NUMBER
, OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUM
, ITEM.LOT_NUMBER CP_LOT_NUMBER
, ITEM.INVENTORY_REVISION REVISION
, ITEM.INSTANCE_DESCRIPTION INSTANCE_NAME
, SI.DESCRIPTION PRODUCT_DESCRIPTION
, 'N' SERVICED_STATUS_FLAG
, OOL.LINE_NUMBER ORIGINAL_LINE_NUMBER
, OOL.ACTUAL_SHIPMENT_DATE SHIPPED_DATE
, ITEM.QUANTITY
, ITEM.EXTERNAL_REFERENCE
, ITEM.LOCATION_TYPE_CODE
, ITEM.OWNER_PARTY_ACCOUNT_ID ACCOUNT_ID
, HZA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SI.ORGANIZATION_ID
, ITEM.LOCATION_ID
, SI.CONCATENATED_SEGMENTS PRODUCT_NAME
, MP.MAINT_ORGANIZATION_ID
, HZP.PARTY_NUMBER CUSTOMER_NUMBER
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZA
, MTL_SYSTEM_ITEMS_VL SI
, OE_ORDER_HEADERS_ALL OOH
, OE_ORDER_LINES_ALL OOL
, CSI_ITEM_INSTANCES ITEM
, CSI_SYSTEMS_TL SYSL
, CSI_SYSTEMS_B SYSB
, CSI_INSTANCE_STATUSES INST
, MTL_PARAMETERS MP
WHERE ITEM.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID
AND ITEM.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND ITEM.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID
AND OOL.HEADER_ID = OOH.HEADER_ID
AND ITEM.SYSTEM_ID = SYSB.SYSTEM_ID(+)
AND SYSL.LANGUAGE(+) = USERENV('LANG')
AND SYSB.SYSTEM_ID = SYSL.SYSTEM_ID (+)
AND ITEM.OWNER_PARTY_ID = HZP.PARTY_ID
AND INST.INSTANCE_STATUS_ID = ITEM.INSTANCE_STATUS_ID
AND INST.INCIDENT_ALLOWED_FLAG ='Y'
AND SI.SERV_REQ_ENABLED_CODE = 'E'
AND SI.COMMS_NL_TRACKABLE_FLAG='Y'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ITEM.ACTIVE_START_DATE)
, TRUNC(SYSDATE))
AND NVL(TRUNC(ITEM.ACTIVE_END_DATE)
, TRUNC(SYSDATE))
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 SI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ITEM.OWNER_PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID(+)