temp

update OSCOUNTRYKIT l
set L.OCKEXPIRE_DATE =(
SELECT MAX(P.OCKEFFECTIVE_DATE) - 1
FROM OSCOUNTRYKIT P
WHERE NVL(P.OCKVENDOR,'OCKODM') = NVL(L.OCKVENDOR,'OCKODM') AND
NVL(P.OCKBRAND,'NULL') = NVL(L.OCKBRAND,'NULL') AND
NVL(P.OCKCOUNTRYCODE,'NULL') = NVL(L.OCKCOUNTRYCODE,'NULL') AND
p.OCKEXPIRE_DATE = l.OCKEXPIRE_DATE
GROUP BY P.OCKBRAND, P.OCKCOUNTRYCODE, P.OCKVENDOR, P.OCKEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.OCKEFFECTIVE_DATE) = l.OCKEFFECTIVE_DATE
)
where exists(
SELECT MAX(P.OCKEFFECTIVE_DATE) - 1
FROM OSCOUNTRYKIT P
WHERE NVL(P.OCKVENDOR,'OCKODM') = NVL(L.OCKVENDOR,'OCKODM') AND
NVL(P.OCKBRAND,'NULL') = NVL(L.OCKBRAND,'NULL') AND
NVL(P.OCKCOUNTRYCODE,'NULL') = NVL(L.OCKCOUNTRYCODE,'NULL') AND
p.OCKEXPIRE_DATE = l.OCKEXPIRE_DATE
GROUP BY P.OCKBRAND, P.OCKCOUNTRYCODE, P.OCKVENDOR, P.OCKEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.OCKEFFECTIVE_DATE) = L.OCKEFFECTIVE_DATE
)