Thursday, 4 September 2014

AR to HZ Table Link


SELECT HP.PARTY_NAME CUSTOMER_NAME
  FROM HZ_PARTIES HP,
       RA_CUSTOMER_TRX_ALL RCTA,
       HZ_CUST_ACCOUNTS_ALL HCAA
 WHERE HP.PARTY_ID = HCAA.PARTY_ID
   AND HCAA.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
   AND RCTA.TRX_NUMBER = '500875'
   AND RCTA.ORG_ID = 204
 
   =======================================
 
   SELECT ACRA.RECEIPT_NUMBER
     FROM RA_CUSTOMER_TRX_ALL RCTA,
          AR_CASH_RECEIPTS_ALL ACRA,
          AR_RECEIVABLE_APPLICATIONS_ALL ARAA
    WHERE RCTA.CUSTOMER_TRX_ID = ARAA.APPLIED_CUSTOMER_TRX_ID
      AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
      AND RCTA.TRX_NUMBER = '500875'
      AND RCTA.ORG_ID = 204
   
      ===============================
   
      SELECT RCTTA.NAME
        FROM RA_CUST_TRX_TYPES_ALL RCTTA,
             RA_CUSTOMER_TRX_ALL RCTA
       WHERE RCTTA.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_ID
         AND RCTA.TRX_NUMBER = '500875'
         AND RCTA.ORG_ID = RCTTA.ORG_ID
         AND RCTA.ORG_ID = 204
       
         =============================================
       
         SELECT HP.PARTY_NAME,
                HCAA.ACCOUNT_NUMBER,
                HL.ADDRESS1,
                HL.ADDRESS2,
                HL.ADDRESS3,
                HL.ADDRESS4,
                HL.CITY,
                HL.STATE,
                HL.COUNTRY
           FROM HZ_LOCATIONS HL,
                HZ_PARTIES HP,
                HZ_PARTY_SITES HPS,
                HZ_CUST_ACCOUNTS_ALL HCAA,
                HZ_CUST_ACCT_SITES_ALL HCASA,
                HZ_CUST_SITE_USES_ALL HCSUA,
                RA_CUSTOMER_TRX_ALL RCTA
          WHERE HP.PARTY_ID = HPS.PARTY_ID
            AND HPS.LOCATION_ID = HL.LOCATION_ID
            AND HP.PARTY_ID = HCAA.PARTY_ID
            AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
            AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
            AND HCSUA.SITE_USE_ID = RCTA.SHIP_TO_SITE_USE_ID
            AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
            AND HCAA.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
            AND HCSUA.SITE_USE_CODE = 'SHIP_TO'
            AND RCTA.TRX_NUMBER = '500875'
         
       
         =============================================
       
         SELECT HP.PARTY_NAME,
                HCAA.ACCOUNT_NUMBER,
                HL.ADDRESS1,
                HL.ADDRESS2,
                HL.ADDRESS3,
                HL.ADDRESS4,
                HL.CITY,
                HL.STATE,
                HL.COUNTRY
           FROM HZ_LOCATIONS HL,
                HZ_PARTIES HP,
                HZ_PARTY_SITES HPS,
                HZ_CUST_ACCOUNTS_ALL HCAA,
                HZ_CUST_ACCT_SITES_ALL HCASA,
                HZ_CUST_SITE_USES_ALL HCSUA,
                RA_CUSTOMER_TRX_ALL RCTA
          WHERE HP.PARTY_ID = HPS.PARTY_ID
            AND HPS.LOCATION_ID = HL.LOCATION_ID
            AND HP.PARTY_ID = HCAA.PARTY_ID
            AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
            AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
            AND HCSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID
            AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
            AND HCAA.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
            AND RCTA.ORG_ID = 204
            AND HCSUA.SITE_USE_CODE = 'BILL_TO'
            AND RCTA.TRX_NUMBER = '500875'   

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete