Query to get 'Party Number' & 'Party Site Name' by Passing 'LOCATION_ID' in APPS R12

 

SELECT JPR.operating_unit,

       JPR.org_id,
       JPR.party_number,
       JPr.party_site_name
FROM   apps.jai_party_regs_v JPR,
       apps.jai_party_reg_lines_v JPL,
       apps.jai_reporting_associations JRA,
       hz_cust_acct_sites_all hcas,
       hz_cust_site_uses_all hcs
WHERE  JPR.party_id = hcas.cust_account_id
       AND JPR.party_site_id = hcas.cust_acct_site_id
       AND JPR.party_reg_id = JPL.party_reg_id(+)
       AND JPR.party_reg_id = JRA.entity_id(+)
       AND JRA.reporting_type_name(+) = 'JAI_TP_IN_STATE'
       AND JPL.regime_code = 'IND GST'
       AND Trunc (SYSDATE) BETWEEN jpl.effective_from AND Trunc (
                                   Nvl (jpl.effective_to, SYSDATE))
       AND Trunc (SYSDATE) BETWEEN JRA.effective_from AND Trunc (
                                   Nvl (JRA.effective_to, SYSDATE))
       AND hcas.cust_acct_site_id = hcs.cust_acct_site_id
       AND hcs.location = <<LOCATION_ID>>; 

(or)

SELECT hps.party_site_number,
       hps.party_site_name,
       hps.*
FROM   hz_party_sites hps
WHERE  location_id = << location_id >> ;