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, hza.account_number , si.organization_id, item.location_id, si.concatenated_segments product_name, hzp.party_number customer_number, address_v.address current_address 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, 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, (Select item.instance_id,l.address1|| DECODE(l.address2,NULL,NULL,';'||l.address2) || DECODE(l.address3,NULL,NULL,';'||l.address3) || DECODE(l.address4,NULL,NULL,';'||l.address4) address, l.city,l.state,l.country,l.province,l.county,l.postal_code from hz_party_sites s ,hz_locations l, csi_item_instances item where s.location_id = l.location_id and item.location_type_code = 'HZ_PARTY_SITES' and item.location_id = s.party_site_id union Select item.instance_id, l.address1|| DECODE(l.address2,NULL,NULL,';'||l.address2) || DECODE(l.address3,NULL,NULL,';'||l.address3) || DECODE(l.address4,NULL,NULL,';'||l.address4) address, l.city,l.state,l.country,l.province,l.county,l.postal_code from hz_locations l, csi_item_instances item where item.location_type_code = 'HZ_LOCATIONS' and item.location_id = l.location_id) address_v 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 and acc.party_account_id = hza.cust_account_id(+) AND item.instance_id = address_v.instance_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
, HZA.ACCOUNT_NUMBER
, SI.ORGANIZATION_ID
, ITEM.LOCATION_ID
, SI.CONCATENATED_SEGMENTS PRODUCT_NAME
, HZP.PARTY_NUMBER CUSTOMER_NUMBER
, ADDRESS_V.ADDRESS CURRENT_ADDRESS
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
, 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
, (SELECT ITEM.INSTANCE_ID
, L.ADDRESS1|| DECODE(L.ADDRESS2
, NULL
, NULL
, ';'||L.ADDRESS2) || DECODE(L.ADDRESS3
, NULL
, NULL
, ';'||L.ADDRESS3) || DECODE(L.ADDRESS4
, NULL
, NULL
, ';'||L.ADDRESS4) ADDRESS
, L.CITY
, L.STATE
, L.COUNTRY
, L.PROVINCE
, L.COUNTY
, L.POSTAL_CODE
FROM HZ_PARTY_SITES S
, HZ_LOCATIONS L
, CSI_ITEM_INSTANCES ITEM
WHERE S.LOCATION_ID = L.LOCATION_ID
AND ITEM.LOCATION_TYPE_CODE = 'HZ_PARTY_SITES'
AND ITEM.LOCATION_ID = S.PARTY_SITE_ID UNION SELECT ITEM.INSTANCE_ID
, L.ADDRESS1|| DECODE(L.ADDRESS2
, NULL
, NULL
, ';'||L.ADDRESS2) || DECODE(L.ADDRESS3
, NULL
, NULL
, ';'||L.ADDRESS3) || DECODE(L.ADDRESS4
, NULL
, NULL
, ';'||L.ADDRESS4) ADDRESS
, L.CITY
, L.STATE
, L.COUNTRY
, L.PROVINCE
, L.COUNTY
, L.POSTAL_CODE
FROM HZ_LOCATIONS L
, CSI_ITEM_INSTANCES ITEM
WHERE ITEM.LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
AND ITEM.LOCATION_ID = L.LOCATION_ID) ADDRESS_V
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
AND ACC.PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID(+)
AND ITEM.INSTANCE_ID = ADDRESS_V.INSTANCE_ID(+)