SQL queries for iSupport, OM, Install base, Service Contracts

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)))))

http://oracle-apps-support.blogspot.com/2010/07/queries-for-use.html