Customer Queries

--++++++++++++++++Customer Details With Receipt Methods ++++++++++++++++++

SELECT   hca.account_number act_no

       , hca.cust_account_id cust_id

       , hcsua.site_use_id

       , hp.party_number

       , hps.party_site_number site_num

       , DECODE(hcasa.status, 'I', 'Inactive', 'A', 'Active', 'Other') status

       , hp.party_name

       , hcsua.location

       , hcsua.site_use_code

       , hcasa.creation_date site_created_date

       , fu.description created_by

       , hcasa.last_update_date site_update_date

       , fu2.description updated_by

       , rcrm.cust_receipt_method_id

       , rcrm.customer_id

       , rcrm.receipt_method_id

       , rcrm.primary_flag

       , rcrm.start_date

       , rcrm.end_date

       , rcrm.site_use_id

       , rcrm.creation_date

    FROM apps.hz_parties hp

       , ar.hz_party_sites hps

       , apps.hz_cust_accounts hca

       , apps.hz_cust_acct_sites_all hcasa

       , apps.hz_cust_site_uses_all hcsua

       , applsys.fnd_user fu

       , applsys.fnd_user fu2

       , apps.ar_receipt_methods arm

       , apps.ra_cust_receipt_methods rcrm

   WHERE hp.party_id = hca.party_id

     AND hp.party_id = hps.party_id

     AND hcasa.party_site_id = hps.party_site_id

     AND hca.cust_account_id = hcasa.cust_account_id

     AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

     AND hcsua.created_by = fu.user_id

     AND hcsua.last_updated_by = fu2.user_id

     AND arm.receipt_method_id = rcrm.receipt_method_id

     AND rcrm.customer_id = hca.cust_account_id

     AND rcrm.site_use_id = hcsua.site_use_id

     AND hcasa.status <> 'I'

     AND hca.cust_account_id = 123

--     AND hca.account_number = '123'

     AND rcrm.end_date IS NULL

     AND 1 = 1

ORDER BY hp.party_name;

--++++++++++++++++Customer Details ++++++++++++++++++

(SELECT distinct party_name,

hca.account_number,

hca.cust_account_id,

hca.account_number,

hca.ORIG_SYSTEM_REFERENCE,

hca.account_name,

hp.party_id,

hp.party_name,

hp.country,

hca.org_id,

hps.party_site_name,

hps.addressee

FROM hz_cust_accounts hca,

hz_parties hp,

hz_party_sites hps,

hz_cust_acct_sites_all hcas,

hz_cust_site_uses_all hcsu

WHERE hca.party_id = hp.party_id

--and c.party_site_id = d.party_site_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcsu.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID

AND hcsu.SITE_USE_CODE = 'BILL_TO'

AND hcsu.org_id = 111

and hps.party_site_id = hcas.party_site_id

AND hcas.org_id = hcsu.org_id

and exists(select 1

from ar_cash_receipts_all

where currency_code = 'CNY'

and customer_site_use_id = hcsu.site_use_id)

AND hp.country = 'CN');

--++++++++++++++++Customer Details ++++++++++++++++++

SELECT hp.party_name,

         hp.party_number,

         hca.account_number,

         hca.ACCOUNT_NAME,

         hcas.ORIG_SYSTEM_REFERENCE                 nav_shiptoid,

         hcsu.ATTRIBUTE14                           siret_num,

         hcsu.LOCATION                              Party_site_name,

         hps.party_site_number,

         hl.ADDRESS1,

         hl.ADDRESS2,

         hl.CITY,

         hl.STATE,

         hl.COUNTRY,

         DECODE (hca.status, 'A', 'Yes', 'No')      Customer_Active,

         DECODE (hcas.status, 'A', 'Yes', 'No')     Site_ACtive

    FROM hz_parties            hp,

         hz_cust_accounts_all  hca,

         hz_cust_acct_sites_all hcas,

         hz_party_sites        hps,

         hz_locations          hl,

         hz_cust_site_uses_all hcsu

   WHERE     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 hl.location_id = hps.location_id

         AND hcsu.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID

         AND hcas.org_id = 814

ORDER BY party_name

-------------------CUSTOMER ACCOUNT CONTACT INFO

select hp.party_name

,hp1.party_name Contact_name

,hoc.contact_number

,hcar.cust_acct_site_id

,hca.cust_account_id

from apps.hz_parties hp

,apps.hz_cust_accounts hca

,apps.hz_org_contacts hoc

,apps.hz_cust_account_roles hcar

,apps.hz_parties hp1

,apps.hz_relationships hr

where 1 = 1

and hca.party_id = hp.party_id

AND hr.subject_id = hp1.party_id

AND hr.object_id = hp.party_id

AND hcar.party_id = hr.party_id

AND hoc.party_relationship_id = hr.relationship_id

AND hcar.cust_acct_site_id is null

AND hcar.STATUS like 'A';

-------------------CUSTOMER ACCOUNT SITE CONTACT INFO

select hp.party_name

,hp1.party_name Contact_name

,hoc.contact_number

,hps.party_site_id

,hps.PARTY_SITE_NUMBER

from apps.hz_parties hp

,apps.hz_party_sites hps

,apps.hz_cust_accounts hca

,apps.hz_cust_acct_sites_all hcas

,apps.hz_org_contacts hoc

,apps.hz_cust_account_roles hcar

,apps.hz_parties hp1

,apps.hz_relationships hr

where 1 = 1

and hp.party_id = hps.party_id

and hca.party_id = hp.party_id

and hca.cust_account_id = hcas.cust_account_id

and hcas.party_site_id = hps.party_site_id

and hcar.cust_acct_site_id(+) = hcas.cust_acct_site_id

AND hr.subject_id = hp1.party_id

AND hr.object_id = hp.party_id

AND hcar.party_id = hr.party_id

AND hoc.party_relationship_id = hr.relationship_id

AND hcar.STATUS like 'A'


--//----------------Customer Relationship Party Details --------------

(SELECT hp.party_name hotel_name,  hp1.party_name,hr.relationship_type,

hca.attribute1 hotel_id,

hca.account_number,

hsua.site_use_id

,hp1.party_name Contact_name

FROM apps.hz_parties hp

,apps.hz_cust_accounts hca

,apps.hz_parties hp1

,apps.hz_relationships hr

,hz_cust_acct_sites_all hcsa,

hz_cust_site_uses_all hsua

WHERE 1 = 1

AND hca.party_id = hp.party_id

AND hr.subject_id = hp.party_id

AND hr.object_id = hp1.party_id

AND hsua.cust_acct_site_id = hcsa.cust_acct_site_id

AND hca.cust_account_id = hcsa.cust_account_id

AND hsua.SITE_USE_CODE = 'BILL_TO'

AND hsua.status = 'A'

--and hr.relationship_type = 'Legal Entity and Property'

and hca.account_number = '965566'

)