VIEW: APPS.CS_SR_NEW_FIND_PROD_MAINT_P_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, cip.relationship_type_code, 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, acc.party_account_id account_id, si.organization_id, item.location_id, si.concatenated_segments product_name FROM hz_parties hzp, 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, csi_ip_accounts acc, (SELECT instance_id, party_id, relationship_type_code, instance_party_id FROM csi_i_parties WHERE party_source_table = 'HZ_PARTIES' AND sysdate BETWEEN nvl(active_start_date, sysdate) AND nvl(active_end_date, sysdate)) cip 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 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 cip.instance_id = item.instance_id AND cip.party_id = hzp.party_id AND acc.instance_party_id(+) = cip.instance_party_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
, CIP.RELATIONSHIP_TYPE_CODE
, 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
, ACC.PARTY_ACCOUNT_ID ACCOUNT_ID
, SI.ORGANIZATION_ID
, ITEM.LOCATION_ID
, SI.CONCATENATED_SEGMENTS PRODUCT_NAME
FROM HZ_PARTIES HZP
, 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
, CSI_IP_ACCOUNTS ACC
, (SELECT INSTANCE_ID
, PARTY_ID
, RELATIONSHIP_TYPE_CODE
, INSTANCE_PARTY_ID
FROM CSI_I_PARTIES
WHERE PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE
, SYSDATE)
AND NVL(ACTIVE_END_DATE
, SYSDATE)) CIP
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 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 CIP.INSTANCE_ID = ITEM.INSTANCE_ID
AND CIP.PARTY_ID = HZP.PARTY_ID
AND ACC.INSTANCE_PARTY_ID(+) = CIP.INSTANCE_PARTY_ID