AR Customer Queries few

SELECT hca.account_number, hps.party_site_number, site_use_code,

       hcsua.LOCATION, hcsua.last_update_date, hcsua.org_id,

       hcsua.primary_flag correct_flag, stg.prd_flag, hcasa.cust_acct_site_id,

       stg.site_use_id

  FROM xxta_primaryflag_use_id18oct stg,

       hz_cust_site_uses_all hcsua,

       hz_cust_acct_sites_all hcasa,

       hz_cust_accounts hca,

       hz_party_sites hps

 WHERE stg.site_use_id = hcsua.site_use_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

   AND hcasa.cust_account_id = hca.cust_account_id

   AND hps.party_site_id = hcasa.party_site_id

   AND stg.prd_status = hcsua.status

   AND hcsua.status <> 'I'

   AND stg.site_use_id NOT IN (

          SELECT site_use_id

            FROM bolinf.xxta_cust_site_bill_addr_stg

           WHERE status_stg <> 'LE'

          UNION

          SELECT site_use_id

            FROM bolinf.xxta_bio_sites_inactive_stg)

   AND stg.prd_flag <> hcsua.primary_flag

   AND hcsua.org_id <> 866

SELECT hou.NAME, hp.party_name, hca.account_number, hps.party_site_number,

       ca.status, su.LOCATION, su.site_use_code,

       su.primary_flag site_use_primary_flag, su.status site_use_status,

       su.cust_acct_site_id, su.site_use_id

  FROM hz_cust_site_uses_all su,

       hz_cust_acct_sites_all ca,

       hz_cust_accounts hca,

       hz_party_sites hps,

       hz_parties hp,

       hr_operating_units hou

 WHERE 1 = 1

   AND su.cust_acct_site_id = ca.cust_acct_site_id

   AND ca.cust_account_id = hca.cust_account_id

   AND ca.party_site_id = hps.party_site_id

   AND hca.party_id = hp.party_id

   AND hps.party_id = hp.party_id

   AND hou.organization_id = ca.org_id

   AND (   TRUNC (su.last_update_date) >= TRUNC (SYSDATE - 4)

        OR TRUNC (ca.last_update_date) >= TRUNC (SYSDATE - 4)

       )

   AND su.last_updated_by = 15062

   AND ca.org_id <> 866;