Query to get Customer Address that doesn't have Geography Reference in Oracle APPS R12

SELECT hca.account_number

      ,hca.account_name

      ,hcs_ship.site_use_code

      ,hl_ship.address1

      ,hl_ship.state

      ,hl_ship.county

      ,hl_ship.city

      ,hl_ship.postal_code

  FROM hz_cust_site_uses_all hcs_ship

      ,hz_cust_acct_sites_all hca_ship

      ,hz_cust_accounts hca

      ,hz_party_sites hps_ship

      ,hz_locations hl_ship

 WHERE hca.cust_account_id=hca_ship.cust_account_id(+)

   AND hcs_ship.cust_acct_site_id(+) = hca_ship.cust_acct_site_id

   AND hca_ship.party_site_id = hps_ship.party_site_id

   AND hps_ship.location_id = hl_ship.location_id

   AND hca.status='A'

   AND hcs_ship.status='A'

   AND hca_ship.status='A'

   AND hl_ship.country='US'

   --AND hca.account_number='1234567890'

   AND NOT EXISTS (SELECT 1 

                     FROM hz_geographies hg

                    WHERE hg.geography_element2_code=hl_ship.state

                      AND UPPER(hl_ship.county)=UPPER(hg.geography_element3_code)

                      AND UPPER(hl_ship.city)=UPPER(hg.geography_element4_code)

                      AND SYSDATE BETWEEN hg.start_date AND hg.end_date

  )

Query to get Customer Details in Oracle Fusion

select party.party_id

      ,party.party_number

      ,party.party_name customer_name

      ,(select party_nm.attribute1 from HZ_PERSON_PROFILES party_nm where party_nm.party_id = party.party_id) person_name_global

      ,party.party_type 

      ,party.orig_system_reference party_orig_system_reference

      ,party.sales_account_id

      ,cust_account.cust_account_id

      ,cust_account.account_number 

      ,cust_account.customer_class_code

      ,cust_account.orig_system_reference cust_orig_system_reference

      ,cust_accts.cust_acct_site_id

      ,cust_accts.party_site_id

      ,cust_accts.orig_system_reference cust_site_orig_system_reference

      ,cust_accts.bill_to_flag

      ,cust_accts.ship_to_flag

      ,party_site.location_id

      ,party_site.party_site_number

      ,party_site.party_site_name

      ,party_site.orig_system_reference party_site_orig_system_reference

      ,site_use.site_use_id

      ,site_use.site_use_code

      ,site_use.primary_flag

      ,site_use.location

      ,site_use.orig_system_reference site_use_orig_system_reference

      ,ref_accts.bu_id

      ,ref_accts.ledger_id

      ,ledger.name ledger_name

      ,ref_accts.rev_ccid

      ,ref_accts.rec_ccid

      ,rev_gcc.segment1||'-'||rev_gcc.segment2||'-'||rev_gcc.segment3||'-'||rev_gcc.segment4||'-'||rev_gcc.segment5||'-'||rev_gcc.segment6||'-'||rev_gcc.segment7||'-'||rev_gcc.segment8 revenue_account

      ,rec_gcc.segment1||'-'||rec_gcc.segment2||'-'||rec_gcc.segment3||'-'||rec_gcc.segment4||'-'||rec_gcc.segment5||'-'||rec_gcc.segment6||'-'||rec_gcc.segment7||'-'||rec_gcc.segment8 receivable_account

from hz_parties party

    ,hz_cust_accounts cust_account

    ,hz_cust_acct_sites_all cust_accts

    ,hz_party_sites party_site

    ,hz_cust_site_uses_all site_use

    ,ar_ref_accounts_all ref_accts

    ,gl_code_combinations rev_gcc

    ,gl_code_combinations rec_gcc

    ,gl_ledgers ledger

where party_name = 'AYMAN ABDULMOHSEN AL-OJAIMI'

  and party.party_id = cust_account.party_id

  and cust_account.cust_account_id = cust_accts.cust_account_id

  and cust_accts.party_site_id = party_site.party_site_id

  and cust_accts.cust_acct_site_id = site_use.cust_acct_site_id

  --AND site_use.primary_flag = 'Y'

  AND ref_accts.source_ref_table(+) = 'HZ_CUST_SITE_USES_ALL'

  and ref_accts.source_ref_account_id(+) = site_use.site_use_id

  and ref_accts.rev_ccid = rev_gcc.code_combination_id(+)

  and ref_accts.rec_ccid = rec_gcc.code_combination_id(+)

  and ref_accts.ledger_id = ledger.ledger_id(+)

Query to get Customer Details in Oracle APPS R12

SELECT DISTINCT ou.name ou_name 

      ,ps.customer_id

  ,party.party_name customer_name

      ,hzca.account_number customer_number

  ,hzca.status customer_status

  ,hcsu.location

  ,hcsu.site_use_code

  ,hcsu.status location_status

  ,ps.class

  ,hcsu.site_use_id

  ,hcpc.name customer_profile_name

  ,hzl.address1 address_line1

  ,hzl.address2 address_line2

  ,hzl.address3 address_line3

  ,hzl.city

  ,hzl.state

  ,hzl.postal_code  

  ,ps.customer_site_use_id

  ,party_site.identifying_address_flag

  ,ps.trx_date

  FROM apps.hz_parties party

      ,apps.hz_party_sites party_site

  ,apps.hz_locations hzl

  ,apps.hz_cust_accounts hzca

  ,apps.hz_cust_acct_sites hcas

  ,apps.hz_cust_site_uses hcsu

  ,apps.hz_customer_profiles cust_profile

  ,apps.hz_cust_profile_classes hcpc

  ,apps.ar_payment_schedules_all ps

  ,apps.hr_operating_units ou 

 WHERE party.party_id = hzca.party_id(+) 

   AND party.party_id = cust_profile.party_id 

   AND party.party_id = party_site.party_id 

   AND party_site.party_site_id = hcas.party_site_id 

   AND party_site.location_id = hzl.location_id 

   AND hzca.cust_account_id = hcas.cust_account_id 

   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id 

   AND hzca.cust_account_id = cust_profile.cust_account_id 

   AND hzca.cust_account_id = ps.customer_id 

   AND cust_profile.profile_class_id = hcpc.profile_class_id 

   AND ps.customer_site_use_id = hcsu.site_use_id 

   AND hcsu.org_id = ou.organization_id

Customer Credit Limits API in Oracle Apps

DECLARE 

l_cust_prof_amt_rec hz_customer_profile_v2pub.cust_profile_amt_rec_type;

l_version_num NUMBER;

l_return_status     VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHAR2(4000);

BEGIN

l_cust_prof_amt_rec.cust_acct_profile_amt_id :=hz_cust_profile_amts.cust_acct_pr ofile_amt_id;

l_cust_prof_amt_rec.trx_credit_limit := 0; 

l_cust_prof_amt_rec.overall_credit_limit :=1000; 

l_version_num := l_cbd_rec (l_cbd).object_version_number; 

hz_customer_profile_v2pub.update_cust_profile_amt(

'T'

,l_cust_prof_amt_rec

,l_version_num

,l_return_status

,l_msg_count

,l_msg_data 

); 

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Main Exception '||SQLERRM);

END;

Query to get Customer Related Information in Oracle Apps R12

 SELECT party.party_id

      ,cust_acct.cust_account_id

  ,party_site.party_site_id

  ,party_site.location_id

  ,cust_acct_site.cust_acct_site_id

  ,cust_site_use.site_use_id

  ,cust_site_use.site_use_code

  ,party.party_name

  ,location.address1

  ,location.address2

  ,location.city

  ,location.state

  ,location.postal_code

  FROM hz_parties party

      ,hz_cust_accounts cust_acct

  ,hz_party_sites party_site

  ,hz_cust_acct_sites_all cust_acct_site

  ,hz_cust_site_uses_all cust_site_use

  ,hz_locations location

 WHERE party.party_id = cust_acct.party_id

   AND party_site.party_id = party.party_id

   AND party_site.party_site_id = cust_acct_site.party_site_id

   AND cust_acct_site.cust_account_id = cust_acct.cust_account_id

   AND cust_site_use.cust_acct_site_id = cust_acct_site.cust_acct_site_id

   AND cust_site_use.site_use_code = 'BILL_TO'

   AND party_site.location_id = location.location_id

   AND cust_acct.account_number = '123456789'