-- Customer Contact Details :
select account_number "Account Number"
, obj.party_name "Customer Name"
, sub.party_name "Contact Name"
, hcp.contact_point_type || ': ' ||
DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
, 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
, 'WEB' , hcp.url
, 'Unknow contact Point Type ' || hcp.contact_point_type
) "How to Contact"
from apps.hz_cust_accounts hca
, apps.hz_parties obj
, apps.hz_relationships rel
, apps.hz_contact_points hcp
, apps.hz_parties sub
where hca.party_id = rel.object_id
and hca.party_id = obj.party_id
and rel.subject_id = sub.party_id
and rel.relationship_type = 'CONTACT'
and rel.directional_flag = 'F'
and rel.party_id = hcp.owner_table_id
and hcp.owner_table_name = 'HZ_PARTIES'
AND obj.party_name LIKE 'Oxford Unive%';
============================ Customer ==================================
SELECT hl.orig_system_reference,hl.LAST_UPDATE_DATE
,hl.country
,hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.postal_code
,hl.state
,hl.province
,hl.county
,hl.content_source_type
,hl.actual_content_source actual_content_source
,hps.party_site_number
,hps.identifying_address_flag
,hps.status
,hps.party_site_name
,hps.created_by_module
,hps.actual_content_source site_actual_content_source
,hcasa.orig_system_reference site_orig_system_reference
,hcasa.status acct_site_status
,hp.party_number
,hca.account_number
,hou.NAME operting_unit_name
,bill_to_flag
FROM apps.hz_locations hl
,apps.hz_party_sites hps
,apps.hz_cust_acct_sites_all hcasa
,apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hr_operating_units hou
WHERE hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.org_id = hou.organization_id
AND hp.party_type ='ORGANIZATION'
AND hps.status = 'A'
AND hcasa.status = 'A'
AND hp.status = 'A'
AND hca.status = 'A'
-- AND hl.address1 like '8900%Uni%';
-- AND hp.party_number = '14613899';
AND hp.party_name = 'Stanford University';
============================ Customer ==================================
SELECT
----------------------------------------------------------
-- Party Information
----------------------------------------------------------
hp.party_number "Registry ID",
hp.party_name "Party Name",
hp.party_type "Party Type",
DECODE(hp.status,
'A', 'Active',
'I', 'Inactive',
hp.status) "Party Status",
----------------------------------------------------------
-- Account Information
----------------------------------------------------------
hca.account_number "Account Number",
DECODE(hca.status,
'A', 'Active',
'I', 'Inactive',
hca.status) "Account Status",
hca.account_name "Account Description",
hca.customer_class_code "Classification",
DECODE(hca.customer_type,
'R', 'External',
'I', 'Internal',
hca.customer_type) "Account Type",
----------------------------------------------------------
-- Site Information
----------------------------------------------------------
hps.party_site_number "Customer Site Number",
DECODE(hcas.status,
'A', 'Active',
'Inactive') "Site Status",
DECODE(hcas.bill_to_flag,
'P', 'Primary',
'Y', 'Yes',
hcas.bill_to_flag) "Bill To Flag",
DECODE(hcas.ship_to_flag,
'P', 'Primary',
'Y', 'Yes',
hcas.ship_to_flag) "Ship To Flag",
hcas.cust_acct_site_id "Customer Acct Site ID",
----------------------------------------------------------
-- Address Information
----------------------------------------------------------
hl.address1 "Address1",
hl.address2 "Address2",
hl.address3 "Address3",
hl.address4 "Address4",
hl.city "City",
hl.state "State",
hl.postal_code "Zip Code",
ter.name "Territory",
----------------------------------------------------------
-- Collector Information
----------------------------------------------------------
col.name "Collector Name",
----------------------------------------------------------
-- Account Profile Information
----------------------------------------------------------
hcp.credit_checking "Credit Check Flag",
hcp.credit_hold "Credit Hold Flag",
hcpa.auto_rec_min_receipt_amount "Min Receipt Amount",
hcpa.overall_credit_limit "Credit Limit",
hcpa.trx_credit_limit "Order Credit Limit",
----------------------------------------------------------
-- Attachment Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.fnd_documents_vl doc,
apps.fnd_lobs blo,
apps.fnd_attached_documents att
WHERE doc.media_id = blo.file_id
AND doc.document_id = att.document_id
AND att.entity_name = 'AR_CUSTOMERS'
AND att.pk1_value = hca.cust_account_id
AND ROWNUM = 1), 'N'
) "Attachment Flag",
----------------------------------------------------------
-- Party Relationship Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_cust_acct_relate_all hzcar
WHERE hzcar.cust_account_id = hca.cust_account_id
AND hzcar.relationship_type = 'ALL'
AND ROWNUM = 1), 'N'
) "Party Relationship Flag",
----------------------------------------------------------
-- Account Relationship Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_cust_acct_relate_all hzcar
WHERE hzcar.cust_account_id = hca.cust_account_id
AND ROWNUM = 1), 'N'
) "Account Relationship Flag",
----------------------------------------------------------
-- Party Contact Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_parties hp2
WHERE 1=1
AND hp2.party_id = hp.party_id
AND (
hp2.url IS NOT NULL OR
-- LENGTH(TRIM(hp.email_address)) > 5
INSTR(hp2.email_address, '@') > 0 OR
hp2.primary_phone_purpose IS NOT NULL
)
), 'N'
) "Party Contact Flag",
----------------------------------------------------------
-- Account Contact Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_contact_points
WHERE status = 'A'
AND owner_table_id =
(SELECT hcar.party_id
FROM apps.hz_cust_account_roles hcar,
apps.ar_contacts_v acv
WHERE hcar.cust_account_id = hca.cust_account_id
AND hcar.cust_account_role_id = acv.contact_id
AND hcar.cust_acct_site_id IS NULL -- look for account level only
AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
)
AND ROWNUM = 1), 'N'
) "Account Contact Flag",
----------------------------------------------------------
-- Site Contact Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_contact_points
WHERE status = 'A'
AND owner_table_id =
(
SELECT hcar.party_id
FROM apps.hz_cust_account_roles hcar,
apps.ar_contacts_v acv
WHERE hcar.cust_acct_site_id = hcas.cust_acct_site_id
AND hcar.cust_account_role_id = acv.contact_id
AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
)
AND ROWNUM = 1), 'N' -- any contact (email, phone, fax) would suffice this condition
) "Site Contact Flag"
FROM
apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_customer_profiles hcp,
apps.hz_cust_profile_amts hcpa,
apps.hz_locations hl,
apps.ra_territories ter,
apps.ar_collectors col
WHERE
1=1
AND party_name like 'Florida Power%'
AND hp.party_id = hca.party_id
AND hca.cust_account_id = hcas.cust_account_id(+)
AND hps.party_site_id(+) = hcas.party_site_id
AND hp.party_id = hcp.party_id
AND hca.cust_account_id = hcp.cust_account_id
AND hps.location_id = hl.location_id(+)
AND col.collector_id = hcp.collector_id
AND hcas.territory_id = ter.territory_id(+)
AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
-- AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types
-- AND hp.status = 'A' -- only Active Parties/Customers
AND hp.party_number = 8899
ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;
select account_number "Account Number"
, obj.party_name "Customer Name"
, sub.party_name "Contact Name"
, hcp.contact_point_type || ': ' ||
DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
, 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
, 'WEB' , hcp.url
, 'Unknow contact Point Type ' || hcp.contact_point_type
) "How to Contact"
from apps.hz_cust_accounts hca
, apps.hz_parties obj
, apps.hz_relationships rel
, apps.hz_contact_points hcp
, apps.hz_parties sub
where hca.party_id = rel.object_id
and hca.party_id = obj.party_id
and rel.subject_id = sub.party_id
and rel.relationship_type = 'CONTACT'
and rel.directional_flag = 'F'
and rel.party_id = hcp.owner_table_id
and hcp.owner_table_name = 'HZ_PARTIES'
AND obj.party_name LIKE 'Oxford Unive%';
============================ Customer ==================================
SELECT hl.orig_system_reference,hl.LAST_UPDATE_DATE
,hl.country
,hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.postal_code
,hl.state
,hl.province
,hl.county
,hl.content_source_type
,hl.actual_content_source actual_content_source
,hps.party_site_number
,hps.identifying_address_flag
,hps.status
,hps.party_site_name
,hps.created_by_module
,hps.actual_content_source site_actual_content_source
,hcasa.orig_system_reference site_orig_system_reference
,hcasa.status acct_site_status
,hp.party_number
,hca.account_number
,hou.NAME operting_unit_name
,bill_to_flag
FROM apps.hz_locations hl
,apps.hz_party_sites hps
,apps.hz_cust_acct_sites_all hcasa
,apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hr_operating_units hou
WHERE hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.org_id = hou.organization_id
AND hp.party_type ='ORGANIZATION'
AND hps.status = 'A'
AND hcasa.status = 'A'
AND hp.status = 'A'
AND hca.status = 'A'
-- AND hl.address1 like '8900%Uni%';
-- AND hp.party_number = '14613899';
AND hp.party_name = 'Stanford University';
============================ Customer ==================================
SELECT
----------------------------------------------------------
-- Party Information
----------------------------------------------------------
hp.party_number "Registry ID",
hp.party_name "Party Name",
hp.party_type "Party Type",
DECODE(hp.status,
'A', 'Active',
'I', 'Inactive',
hp.status) "Party Status",
----------------------------------------------------------
-- Account Information
----------------------------------------------------------
hca.account_number "Account Number",
DECODE(hca.status,
'A', 'Active',
'I', 'Inactive',
hca.status) "Account Status",
hca.account_name "Account Description",
hca.customer_class_code "Classification",
DECODE(hca.customer_type,
'R', 'External',
'I', 'Internal',
hca.customer_type) "Account Type",
----------------------------------------------------------
-- Site Information
----------------------------------------------------------
hps.party_site_number "Customer Site Number",
DECODE(hcas.status,
'A', 'Active',
'Inactive') "Site Status",
DECODE(hcas.bill_to_flag,
'P', 'Primary',
'Y', 'Yes',
hcas.bill_to_flag) "Bill To Flag",
DECODE(hcas.ship_to_flag,
'P', 'Primary',
'Y', 'Yes',
hcas.ship_to_flag) "Ship To Flag",
hcas.cust_acct_site_id "Customer Acct Site ID",
----------------------------------------------------------
-- Address Information
----------------------------------------------------------
hl.address1 "Address1",
hl.address2 "Address2",
hl.address3 "Address3",
hl.address4 "Address4",
hl.city "City",
hl.state "State",
hl.postal_code "Zip Code",
ter.name "Territory",
----------------------------------------------------------
-- Collector Information
----------------------------------------------------------
col.name "Collector Name",
----------------------------------------------------------
-- Account Profile Information
----------------------------------------------------------
hcp.credit_checking "Credit Check Flag",
hcp.credit_hold "Credit Hold Flag",
hcpa.auto_rec_min_receipt_amount "Min Receipt Amount",
hcpa.overall_credit_limit "Credit Limit",
hcpa.trx_credit_limit "Order Credit Limit",
----------------------------------------------------------
-- Attachment Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.fnd_documents_vl doc,
apps.fnd_lobs blo,
apps.fnd_attached_documents att
WHERE doc.media_id = blo.file_id
AND doc.document_id = att.document_id
AND att.entity_name = 'AR_CUSTOMERS'
AND att.pk1_value = hca.cust_account_id
AND ROWNUM = 1), 'N'
) "Attachment Flag",
----------------------------------------------------------
-- Party Relationship Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_cust_acct_relate_all hzcar
WHERE hzcar.cust_account_id = hca.cust_account_id
AND hzcar.relationship_type = 'ALL'
AND ROWNUM = 1), 'N'
) "Party Relationship Flag",
----------------------------------------------------------
-- Account Relationship Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_cust_acct_relate_all hzcar
WHERE hzcar.cust_account_id = hca.cust_account_id
AND ROWNUM = 1), 'N'
) "Account Relationship Flag",
----------------------------------------------------------
-- Party Contact Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_parties hp2
WHERE 1=1
AND hp2.party_id = hp.party_id
AND (
hp2.url IS NOT NULL OR
-- LENGTH(TRIM(hp.email_address)) > 5
INSTR(hp2.email_address, '@') > 0 OR
hp2.primary_phone_purpose IS NOT NULL
)
), 'N'
) "Party Contact Flag",
----------------------------------------------------------
-- Account Contact Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_contact_points
WHERE status = 'A'
AND owner_table_id =
(SELECT hcar.party_id
FROM apps.hz_cust_account_roles hcar,
apps.ar_contacts_v acv
WHERE hcar.cust_account_id = hca.cust_account_id
AND hcar.cust_account_role_id = acv.contact_id
AND hcar.cust_acct_site_id IS NULL -- look for account level only
AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
)
AND ROWNUM = 1), 'N'
) "Account Contact Flag",
----------------------------------------------------------
-- Site Contact Flag
----------------------------------------------------------
NVL((SELECT 'Y'
FROM apps.hz_contact_points
WHERE status = 'A'
AND owner_table_id =
(
SELECT hcar.party_id
FROM apps.hz_cust_account_roles hcar,
apps.ar_contacts_v acv
WHERE hcar.cust_acct_site_id = hcas.cust_acct_site_id
AND hcar.cust_account_role_id = acv.contact_id
AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
)
AND ROWNUM = 1), 'N' -- any contact (email, phone, fax) would suffice this condition
) "Site Contact Flag"
FROM
apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_customer_profiles hcp,
apps.hz_cust_profile_amts hcpa,
apps.hz_locations hl,
apps.ra_territories ter,
apps.ar_collectors col
WHERE
1=1
AND party_name like 'Florida Power%'
AND hp.party_id = hca.party_id
AND hca.cust_account_id = hcas.cust_account_id(+)
AND hps.party_site_id(+) = hcas.party_site_id
AND hp.party_id = hcp.party_id
AND hca.cust_account_id = hcp.cust_account_id
AND hps.location_id = hl.location_id(+)
AND col.collector_id = hcp.collector_id
AND hcas.territory_id = ter.territory_id(+)
AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
-- AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types
-- AND hp.status = 'A' -- only Active Parties/Customers
AND hp.party_number = 8899
ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;
No comments:
Post a Comment