Customer bill to data with credit limit

select substrb(PARTY.PARTY_NAME,1,50) customer_name,
CUST_ACCT.ACCOUNT_NUMBER customer_number,
party.status customer_status,
party.creation_date Customer_Creation_date,
party.last_update_date Customer_Last_Update_Date,
cust_acct.customer_class_code,
sua.site_use_code,
decode(sua.status,'A','Active','I','Inactive') site_status,
sua.location,
loc.address1||','||loc.address2||','||loc.address3||','||loc.address4||','||loc.city||','||loc.state||','||loc.postal_code||','||loc.country address,
CPV.STANDARD_TERMS_NAME SITE_TERMS,
(Select Aa.Standard_Terms_Name From Ar_Customer_Profiles_V Aa Where CUST_ACCT.CUST_ACCOUNT_ID = Aa.Customer_Id And Aa.Site_Use_Id Is Null) Customer_Header_Terms,
cpv.Profile_Class_Name,
cpv.Collector_Name,
AMT.TRX_CREDIT_LIMIT ORDER_CREDIT_LIMIT,
amt.overall_credit_limit
FROM HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACC_SITE,
HZ_CUST_SITE_USES_ALL SUA,
AR_CUSTOMER_PROFILES_V CPV,
HZ_CUST_PROFILE_AMTS AMT
WHERE PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
AND PARTY.PARTY_ID=PARTY_SITE.PARTY_ID
AND PARTY_SITE.PARTY_SITE_ID=ACC_SITE.PARTY_SITE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID =ACC_SITE.CUST_ACCOUNT_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND ACC_SITE.CUST_ACCT_SITE_ID = SUA.CUST_ACCT_SITE_ID
and sua.site_use_code = 'BILL_TO'
AND CPV.SITE_USE_ID(+) = SUA.SITE_USE_ID
AND CPV.CUSTOMER_PROFILE_ID = AMT.CUST_ACCOUNT_PROFILE_ID(+)
AND PARTY.STATUS='A'
and ACC_SITE.org_id= P_operating_unit;

11i to R12.1.3 key queries

• R12.1.3  for apps.ra_site_uses_all

              (select location, site_use_id, org_id, CUST_ACCT_SITE_ID address_id
              , attribute14, site_use_code
              from HZ_CUST_SITE_USES_ALL)

• R12.1.3 for ra_addresses_all

          (select loc.country, ACCT_SITE.CUST_ACCT_SITE_ID address_id , province , postal_code, state ,loc.country country_code, city

          , address1, address2, address3, address4 , CUST_ACCOUNT_ID customer_id
          from HZ_PARTY_SITES PARTY_SITE,
          HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
          HZ_LOCATIONS LOC,
          HZ_CUST_ACCT_SITES_ALL ACCT_SITE
          WHERE ACCT_SITE.PARTY_SITE_ID     = PARTY_SITE.PARTY_SITE_ID
          AND LOC.LOCATION_ID               = PARTY_SITE.LOCATION_ID
          AND LOC.LOCATION_ID               = LOC_ASSIGN.LOCATION_ID
          AND NVL ( ACCT_SITE.ORG_ID, -99 ) = NVL ( LOC_ASSIGN.ORG_ID, -99 ) )

• R12.1.3 for ra_customers 

(select substrb(PARTY.PARTY_NAME,1,50) customer_name,
CUST_ACCT.ACCOUNT_NUMBER customer_number,
CUST_ACCT.CUST_ACCOUNT_ID customer_id,
PARTY.PARTY_ID party_id, PARTY.STATUS
from HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
where CUST_ACCT.PARTY_ID = PARTY.PARTY_ID)

• Customer data for Ship_to
  select HCA.ACCOUNT_NUMBER
  , HP.PARTY_NAME CUSTOMER_NAME
  , HCSUA.SITE_USE_CODE
  , (hp.address1 || ','|| hp.address2 ||','||hp.address3 || ',' ||hp.address4 ||','||hp.country) address
  , HCSUA.FREIGHT_TERM
  , NVL2(HCSUA.PAYMENT_TERM_ID,(select name from RA_TERMS where TERM_ID = HCSUA.PAYMENT_TERM_ID), null) PAYMENT_TERM
  --,  HCSUA.SITE_USE_ID
  --, HCSUA.CUST_ACCT_SITE_ID
  --, HCASA.CUST_ACCOUNT_ID customer_id
  from
  HZ_CUST_SITE_USES_ALL HCSUA ,
  HZ_CUST_ACCT_SITES_ALL HCASA,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP
  where 1=1
  and HCSUA.SITE_USE_CODE  ='SHIP_TO'
  and HCSUA.ORG_ID = -- operating_unit
  and HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
  and HCASA.ORG_ID =
  and HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
  and HP.PARTY_ID = HCA.PARTY_ID
  and hca.status = 'A'
  and HCSUA.STATUS = 'A'
  and HCASA.STATUS = 'A';

Customer to find from location on R12.1.3

I HAVE CUSTOM SHIP TO LOCATION, AND ITS ID NOW I HAVE TO TRACK THE CUSTOMER NAME.

SELECT * FROM HZ_LOCATIONS WHERE LOCATION_ID = 338403;

FOR RA_ADDRESSES_ALL THE VIEW
IS  DERIVED FROM :
  SELECT LOC.COUNTRY,
    ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID ,
    PROVINCE ,
    POSTAL_CODE,
    STATE ,
    LOC.COUNTRY COUNTRY_CODE,
    CITY ,
    ADDRESS1,
    ADDRESS2,
    ADDRESS3,
    ADDRESS4 ,
    CUST_ACCOUNT_ID CUSTOMER_ID
  FROM HZ_PARTY_SITES PARTY_SITE,
    HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
    HZ_LOCATIONS LOC,
    HZ_CUST_ACCT_SITES_ALL ACCT_SITE
  WHERE ACCT_SITE.PARTY_SITE_ID     = PARTY_SITE.PARTY_SITE_ID
  AND LOC.LOCATION_ID               = PARTY_SITE.LOCATION_ID
  AND LOC.LOCATION_ID               = LOC_ASSIGN.LOCATION_ID
  AND NVL ( ACCT_SITE.ORG_ID, -99 ) = NVL ( LOC_ASSIGN.ORG_ID, -99 ) NOW WE NEED TO DIG
  INTO HZ_PARTY_SITES FOR THIS LOCATION_ID
  SELECT * FROM HZ_PARTY_SITES WHERE LOCATION_ID = 338403; --the same location which we have
  THERE
IS
  PARTY_SITE_ID WHICH WHICH
IS
  USED TO GET CUSTOMER DETAILS FROM HZ_CUST_ACCT_SITES_ALL WHICH GIVEN ME CORRECT CUSTOMER_ACCT_ID.


BINGO !