Queries for Use

Isupport
---------
SELECT p3.party_name Customername, p3.party_number Customernumber, p1.person_first_name' 'p1.person_middle_name' 'p1.person_last_name Contactname, hzc1.email_address Emailaddress, hzc2.raw_phone_number Primaryphonenumber, hca.cust_account_id, p3.party_id
FROM ibu_multi_parties mul,
hz_parties p1,
hz_parties p3,
hz_relationships r,
fnd_user u,
apps.hz_cust_accounts hca,
apps.HZ_CONTACT_POINTS hzc1,
apps.HZ_CONTACT_POINTS hzc2,
apps.hz_party_preferences pref
WHERE mul.party_id = r.party_id
AND mul.enable_flag = 'Y'
AND p3.party_type = 'ORGANIZATION'
AND p3.party_id = r.object_id
AND r.subject_id = p1.party_id
AND p1.party_type = 'PERSON'
AND u.employee_id IS NULL
AND r.relationship_code IN ('EMPLOYEE_OF', 'CONTACT_OF')
AND TRUNC (NVL (u.end_date, SYSDATE + 1)) >= TRUNC (SYSDATE)
AND u.user_id NOT IN ( SELECT user_id FROM jtf_um_usertype_reg WHERE status_code IN ('PENDING', 'UPGRADE_APPROVAL_PENDING')) AND u.user_id = mul.user_id
AND u.user_id = fnd_global.user_id
AND hca.party_id = p3.party_id
AND hzc1.owner_table_name(+) = 'HZ_PARTIES'
AND hzc1.contact_point_type(+) ='EMAIL'
AND hzc2.owner_table_name(+) = 'HZ_PARTIES'
AND hzc2.contact_point_type(+) = 'PHONE'
AND hzc1.primary_flag(+) = 'Y'
AND hzc2.primary_flag(+) = 'Y'
AND hzc1.owner_table_id(+) = u.customer_id
AND hzc1.owner_table_id = hzc2.owner_table_id(+)
AND pref.party_id = r.party_id
AND pref.module='IBU_ACCOUNT'
AND pref.category = 'PRIMARY_ACCOUNT'
AND pref.preference_code='CUSTOMER_ACCOUNT_ID'
AND mul.current_party = 'Y'
AND hca.cust_account_id = Decode(pref.value_number,NULL,hca.cust_account_id,0,hca.cust_account_id,pref.value_number)
OM
----
SELECT loc.location_id ShipLocId ,loc.address1', 'loc.address2', 'loc.address3', 'loc.city', 'loc.postal_code', 'loc.state', 'loc.country ShipAdd, hcsu.site_use_id ship_site_use_id
FROM apps.hz_parties hp ,
apps.hz_party_sites hps ,
apps.hz_locations loc ,
apps.hz_cust_accounts_all hca ,
apps.hz_cust_acct_sites_all hcas ,
apps.hz_cust_site_uses_all hcsu
where hps.party_id = hp.party_id
AND loc.location_id = hps.location_id
AND hca.party_id = hp.party_id
AND hcas.party_site_id = hps.party_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcas.CUST_ACCT_SITE_ID = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcas.org_id = hcsu.org_id
AND hcas.org_id = NVL(hca.org_id,hcas.org_id)
AND hca.cust_account_id = NVL(:1,hca.cust_account_id)
AND NVL(hcsu.status,'I') = 'A'
INSTALL BASE
-----------------
SELECT distinct KFV.attribute12 Model_type, CII.serial_number serial_number, HZA.cust_account_id cust_account_id
FROM CSI_ITEM_INSTANCES CII,
CSI_IP_ACCOUNTS CIA,
CSI_I_ORG_ASSIGNMENTS CIOA,
CSI_I_PARTIES CIP,
CSI_II_RELATIONSHIPS CIR,
CSI_INSTANCE_STATUSES CIS,
CSI_LOOKUPS CL,
CSI_LOOKUPS CL3,
CSI_SYSTEMS_VL CSV,
MTL_SYSTEM_ITEMS_KFV KFV,
OE_AGREEMENTS A1,
ASO_I_OE_ORDER_HEADERS_V OOH,
ASO_I_OE_ORDER_LINES_V OOL,
HZ_CUST_ACCOUNTS HZA,
HZ_PARTIES HZP
WHERE CII.SYSTEM_ID = CSV.SYSTEM_ID(+)
AND CII.INSTANCE_TYPE_CODE = CL.LOOKUP_CODE(+)
AND CL.LOOKUP_TYPE(+) = 'CSI_INST_TYPE_CODE'
AND CII.LAST_OE_AGREEMENT_ID = A1.AGREEMENT_ID(+)
AND CII.INSTANCE_STATUS_ID = CIS.INSTANCE_STATUS_ID
AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND CIR.RELATIONSHIP_TYPE_CODE = CL3.LOOKUP_CODE(+)
AND CL3.LOOKUP_TYPE(+) = 'CSI_INSTANCE_VERSION_LABELS'
AND CII.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+)
AND OOL.HEADER_ID = OOH.HEADER_ID(+)
AND HZA.CUST_ACCOUNT_ID= CIA.PARTY_ACCOUNT_ID
AND HZA.PARTY_ID = HZP.PARTY_ID
AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
AND CIR.RELATIONSHIP_TYPE_CODE (+)= 'COMPONENT-OF'
AND CIR.ACTIVE_END_DATE(+)IS NULL
AND CII.INSTANCE_ID = CIOA.INSTANCE_ID (+)
AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM'
AND CII.INSTANCE_ID = CIP.INSTANCE_ID
AND CIP.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID (+)
AND CIA.RELATIONSHIP_TYPE_CODE(+) = 'OWNER'
AND CIA.ACTIVE_END_DATE(+) IS NULL
AND NOT EXISTS ( SELECT 1 FROM CSI_INSTALL_PARAMETERS CIPM WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )
AND CII.serial_number IS NOT NULL;
SERVICE CONTRACTS
------------------------
select DECODE(coverage1,NULL,'No Coverage',coverage1) Service_Coverage,DECODE(warranty1,NULL,'No',warranty1) Warranty from ( Select MAX(DECODE(cov.NAME,'Servicepass Complete',Decode(cle.sts_code,'ACTIVE',cov.NAME,NULL),'Servicepass Standard',Decode(cle.sts_code,'ACTIVE',cov.NAME,NULL),'Qualitypass',Decode(cle.sts_code,'ACTIVE',cov.NAME,NULL),NULL)) coverage1 , MAX(DECODE(cov.NAME,'WARRANTY',Decode(cle.sts_code,'ACTIVE','Yes','No'),NULL)) warranty1
FROM okc_k_lines_b cle,
oks_k_lines_b kln,
okc_k_lines_tl cov,
okc_line_styles_tl lse,
okc_statuses_tl sts,
okc_k_items cit,
OKS_AUTH_LINES_V x
WHERE cle.cle_id IS NULL
AND kln.cle_id = cle.ID
AND lse.ID = cle.lse_id
AND lse.LANGUAGE = USERENV ('LANG')
AND cit.cle_id = cle.ID
AND cit.jtot_object1_code IN ('OKX_SERVICE', 'OKX_WARRANTY', 'OKX_USAGE', 'OKS_SUBSCRIPTION')
AND sts.code = cle.sts_code
--AND sts.meaning NOT IN ('Terminated')
AND sts.LANGUAGE = USERENV ('LANG')
AND cov.ID(+) = kln.coverage_id
AND cov.LANGUAGE(+) = USERENV ('LANG')
AND x.CLE_ID = cle.id
AND ( x.id IN ( select cle_id from okc_k_items where dnz_chr_id = cit.dnz_chr_id and object1_id1 IN (select instance_id from csi_item_instances where 1 = 1 and SERIAL_NUMBER =NVL(:1,SERIAL_NUMBER)))))