[SQL] Problem in query that returns information from Oracle customers by site

My query should have only the records that were modified in sysdate

SELECT
/*********hz_parties******************/
hp.party_id,
hp.party_name,
hp.jgzz_fiscal_code,
To_char(hp.creation_date, 'DD/MM/YYYY'),
/*********hz_party_sites******************/
hps.party_site_id,
hps.party_site_number,
/*********hz_organization_profiles******************/
hop.attribute2,
hop.attribute1,
hop.attribute3,
/*********hz_locations******************/
hzl.attribute6,
hzl.postal_code,
hzl.address1,
hzl.addr_element_attribute3,
hzl.state,
hzl.addr_element_attribute2,
hzl.city,
hzl.attribute1,
CASE
WHEN hzl.attribute7 IS NULL THEN NULL
ELSE (SELECT vst1.description
FROM fnd_vs_values_b VSV1,
fnd_vs_values_tl VST1
WHERE vsv1.value = hzl.attribute7
AND vsv1.attribute_category = 'Periodo de facturación'
AND vsv1.value_id = vst1.value_id
AND vsv1.enterprise_id = vst1.enterprise_id
AND vsv1.sandbox_id = vst1.sandbox_id
AND vst1.language = 'E')
END "PeriodoFacturacionId",
hzl.attribute4 "LimiteCredito",
hzl.attribute3 "ClienteId",
/*********vendor******************/
(SELECT vst1.description
FROM fnd_vs_values_b VSV1,
fnd_vs_values_tl VST1
WHERE vsv1.value = hzl.attribute2
AND vsv1.attribute_category = 'Vendedor'
AND vsv1.value_id = vst1.value_id
AND vsv1.enterprise_id = vst1.enterprise_id
AND vsv1.sandbox_id = vst1.sandbox_id
AND vst1.language = 'E') "Vendedor",
/*********others******************/
FROM hz_parties hp,
hz_party_sites hps,
hz_organization_profiles hop,
hz_locations hzl
WHERE
/*********hz_parties---hz_party_sites******************/
hp.party_type = 'ORGANIZATION'
AND hp.party_id = hps.party_id
/*********hz_parties---hz_organization_profiles******************/
AND hp.party_id = hop.party_id
/*********hz_party_sites---hz_locations******************/
AND hps.location_id = hzl.location_id
AND ((Trunc(HP.CREATION_DATE) = Trunc(SYSDATE)) OR
(Trunc(hop.LAST_UPDATE_DATE) = Trunc(SYSDATE)) OR
Trunc(hzl.LAST_UPDATE_DATE) = Trunc(SYSDATE))


I'm working on a query to get information about customers by site. For example, if a customer has 10 sites and I modify only one, it should skip the other 9 and only return the one I modified, however it gives me the information for the 10 sites in total Of the 4 tables I'm using:

• hz_parties
• hz_party_sites
• hz_organization_profiles
• hz_locations

The change can only occur in the tables:

• hz_organization_profiles
• hz_locations

Continue reading...