VIEW: APPS.CSD_CUSTOMER_SEARCH_V

Source

View Text - Preformatted

SELECT hp.party_id party_id, hp.party_name party_name, hp.party_number party_number, hp.party_type party_type, hca.cust_account_id account_id, hca.account_number account_number, hca.account_name account_name, hc.phone_country_code||''||hc.phone_area_code||''|| hc.phone_number phone_number, hc.phone_line_type phone_type, arl.meaning phone_type_meaning, a.serial_number serial_number, a.instance_id instance_id, a.instance_number instance_number, a.inventory_revision item_revision, a.lot_number lot_number, a.quantity qty, a.unit_of_measure uom, a.mfg_serial_number_flag mfg_serial_number_flag, b.organization_id inventory_org_id, b.inventory_item_id inventory_item_id, b.concatenated_segments product_name, b.organization_id organization_id, b.description product_desc, b.serial_number_control_code serial_number_control_code, b.revision_qty_control_code revision_qty_control_code FROM csi_item_instances a, mtl_system_items_vl b, hz_parties hp, hz_contact_points hc, hz_cust_accounts hca, ar_lookups arl WHERE TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE)) AND TRUNC(NVL(a.active_end_date, SYSDATE)) AND b.inventory_item_id = a.inventory_item_id AND b.organization_id = cs_std.get_item_valdn_orgzn_id AND b.service_item_flag = 'N' AND b.enabled_flag = 'Y' AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS') AND b.serv_req_enabled_code = 'E' AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(b.start_date_active, SYSDATE)) AND TRUNC(NVL(b.end_date_active, SYSDATE)) AND hp.status = 'A' AND hp.party_id = hca.party_id(+) AND hca.status(+) = 'A' AND hp.party_id = hc.owner_table_id(+) AND hc.owner_table_name(+) ='HZ_PARTIES' AND hc.primary_flag(+) = 'Y' AND hc.contact_point_type(+)='PHONE' AND hc.status(+) = 'A' AND a.owner_party_account_id = hca.cust_account_id AND a.owner_party_id = hp.party_id AND arl.lookup_type(+) = 'PHONE_LINE_TYPE' AND arl.lookup_code(+) = hc.phone_line_type

View Text - HTML Formatted

SELECT HP.PARTY_ID PARTY_ID
, HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, HP.PARTY_TYPE PARTY_TYPE
, HCA.CUST_ACCOUNT_ID ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCA.ACCOUNT_NAME ACCOUNT_NAME
, HC.PHONE_COUNTRY_CODE||''||HC.PHONE_AREA_CODE||''|| HC.PHONE_NUMBER PHONE_NUMBER
, HC.PHONE_LINE_TYPE PHONE_TYPE
, ARL.MEANING PHONE_TYPE_MEANING
, A.SERIAL_NUMBER SERIAL_NUMBER
, A.INSTANCE_ID INSTANCE_ID
, A.INSTANCE_NUMBER INSTANCE_NUMBER
, A.INVENTORY_REVISION ITEM_REVISION
, A.LOT_NUMBER LOT_NUMBER
, A.QUANTITY QTY
, A.UNIT_OF_MEASURE UOM
, A.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, B.ORGANIZATION_ID INVENTORY_ORG_ID
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS PRODUCT_NAME
, B.ORGANIZATION_ID ORGANIZATION_ID
, B.DESCRIPTION PRODUCT_DESC
, B.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, B.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
FROM CSI_ITEM_INSTANCES A
, MTL_SYSTEM_ITEMS_VL B
, HZ_PARTIES HP
, HZ_CONTACT_POINTS HC
, HZ_CUST_ACCOUNTS HCA
, AR_LOOKUPS ARL
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(NVL(A.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(NVL(A.ACTIVE_END_DATE
, SYSDATE))
AND B.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND B.SERVICE_ITEM_FLAG = 'N'
AND B.ENABLED_FLAG = 'Y'
AND A.LOCATION_TYPE_CODE IN ('HZ_PARTY_SITES'
, 'HZ_LOCATIONS')
AND B.SERV_REQ_ENABLED_CODE = 'E'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(B.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(B.END_DATE_ACTIVE
, SYSDATE))
AND HP.STATUS = 'A'
AND HP.PARTY_ID = HCA.PARTY_ID(+)
AND HCA.STATUS(+) = 'A'
AND HP.PARTY_ID = HC.OWNER_TABLE_ID(+)
AND HC.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HC.PRIMARY_FLAG(+) = 'Y'
AND HC.CONTACT_POINT_TYPE(+)='PHONE'
AND HC.STATUS(+) = 'A'
AND A.OWNER_PARTY_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND A.OWNER_PARTY_ID = HP.PARTY_ID
AND ARL.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE'
AND ARL.LOOKUP_CODE(+) = HC.PHONE_LINE_TYPE