Sunday, 30 October 2011

How to Find Customers in Oracel Apps a Single Query or View

CREATE OR REPLACE FORCE VIEW APPS.PARTY_CUST_V
(
   PARTY_NAME,
   PARTY_NUMBER,
   ACCOUNT_NUMBER,
   CUST_ACCOUNT_ID,
   CUSTOMER_CATEGORY_CODE,
   PARTY_ID,
   PARTY_SITE_ID,
   LOCATION_ID,
   ADDRESS1,
   ADDRESS2,
   ADDRESS3,
   CITY,
   STATE,
   COUNTRY,
   POSTAL_CODE,
   GEOGRAPHY_NAME
   SITE_USE_CODE,
   SITE_USE_ID,
   ADDRESS_ID,
   PRIMARY_SALESREP_ID,
   SALES_PERSON,
   LOCATION,
   ORG_ID,
   BILL_TO_FLAG,
   CUST_ADDRESS,
   CUST_ACCT_SITE_ID
)
AS

   SELECT   ar.customer_name party_name,
            hp.party_number,
            hca.account_number,
            hca.cust_account_id,
            ar.CUSTOMER_CATEGORY_CODE,
            hp.party_id,
            hps.party_site_id,
            hps.location_id,
            hl.address1,
            hl.address2,
            hl.address3,
            hl.city,
            hl.state,
            hl.country,
            hl.postal_code,
            hg.GEOGRAPHY_NAME,
            hcsu.site_use_code,
            hcsu.site_use_id,
            hcsu.CUST_ACCT_SITE_ID address_id,
            hcsu.PRIMARY_SALESREP_ID,
            (  SELECT   resource_name
                 FROM   jtf_rs_defresources_v j, jtf_rs_salesreps s
                -- oe_order_headers_all h
                WHERE   j.resource_id = s.resource_id
                        AND s.salesrep_id = hcsu.PRIMARY_SALESREP_ID
             GROUP BY   resource_name)
               sales_person,
            hcsu.LOCATION,
            hcsa.ORG_ID,
            hcsa.bill_to_flag,
               SUBSTR (HL.ADDRESS1, 1, 60)
            || ' '
            || SUBSTR (HL.ADDRESS2, 1, 60)
            || ' '
            || LTRIM (
                  hl.city || ' ' || '-' || hl.postal_code || ' ' || hl.state
               )
               cust_address,
            hcsa.CUST_ACCT_SITE_ID
     FROM   ar_customers ar,
            hz_parties hp,
            hz_party_sites hps,
            hz_locations hl,
            hz_cust_accounts_all hca,
            hz_cust_acct_sites_all hcsa,
            hz_cust_site_uses_all hcsu,
            HZ_GEOGRAPHIES HG
    WHERE       hp.party_id = hps.party_id
            AND hps.location_id = hl.location_id
            AND hp.party_id = hca.party_id
            AND hcsa.party_site_id = hps.party_site_id
            AND hps.status = 'A'
            AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
            AND hg.geography_name=hl.country
            AND hcsa.status = 'A'
            AND hca.cust_account_id = hcsa.cust_account_id
            AND hcsu.status = 'A'
            AND ar.customer_id(+) = hca.cust_account_id
            AND ar.status <> 'I'
        --AND hca.cust_account_id=2068

2 comments:

  1. Above script has syntax errors...for example, jtf_rs_defresources_v isn't part of default oracle install

    ReplyDelete
    Replies
    1. It can be used to display the saleperson name as shown in Order Form.

      Delete