Monday, October 20, 2014

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;

No comments: