Poesia.

SELECT DISTINCT prg.ppmpkey , prg.NAME , prg.contractname , gya1.gyaname1 CCNAME, gya1.gyacustnrold , gya1.gyaname2 cc_custid, gya2.gyaname1 KANAME , gya2.gyacustnrold,
gya2.gyaname2 ka_custid--, x.gyapkey
FROM
(SELECT NVL(PZC0.ppmpkey,'NULL') ppmpkey,
ZAC0.gyapkeyc gyapkey,PZC0.datefrom,PZC0.dateto , PZC0.GYAPKEY PZC0GYAKEY
FROM PZC0,ZAC0
WHERE ZAC0.gyapkeya=PZC0.gyapkey
AND ZAC0.regionkey LIKE trim(PZC0.regionkey)||'%'
AND PZC0.ppmpkey IN ('0000000BCGL' , '0000000BCGM' , '0000000BCGP' , '0000000BCGQ',
'0000000BCWQ' , '0000000BCWP' , '0000000BCWQ')
AND PZC0.status NOT IN ('d','D')
-- AND ZAC0.gyapkeyc='0000000Q407'
ORDER BY 2 , 3 ) x ,
(SELECT NVL(PZC0.ppmpkey,'NULL') ppmpkey,
ZAC0.gyapkeyc gyapkey,PZC0.datefrom,PZC0.dateto , PZC0.GYAPKEY PZC0GYAKEY
FROM PZC0,ZAC0
WHERE ZAC0.gyapkeya=PZC0.gyapkey
AND ZAC0.regionkey LIKE trim(PZC0.regionkey)||'%'
AND PZC0.ppmpkey IN('0000000BCGL' , '0000000BCGM' , '0000000BCGP' , '0000000BCGQ',
'0000000BCWQ' , '0000000BCWP' , '0000000BCWQ')
AND PZC0.status NOT IN ('d','D')
-- AND ZAC0.gyapkeyc='0000000Q407'
) y , prg , GYA0 gya1 , GYA0 gya2 ,
(SELECT ppmpkey , gyapkey , COUNT(gyapkey)
FROM (
SELECT NVL(PZC0.ppmpkey,'NULL') ppmpkey,
ZAC0.gyapkeyc gyapkey,PZC0.datefrom,PZC0.dateto , PZC0.GYAPKEY PZC0GYAKEY
FROM PZC0,ZAC0
WHERE ZAC0.gyapkeya=PZC0.gyapkey
AND ZAC0.regionkey LIKE trim(PZC0.regionkey)||'%'
AND PZC0.ppmpkey IN ('0000000BCGL' , '0000000BCGM' , '0000000BCGP' , '0000000BCGQ',
'0000000BCWQ' , '0000000BCWP' , '0000000BCWQ')
AND PZC0.status NOT IN ('d','D'))
GROUP BY gyapkey , ppmpkey
HAVING COUNT(gyapkey) > 1
ORDER BY 1
) dup
WHERE x.ppmpkey=y.ppmpkey AND
X.gyapkey = y.gyapkey AND
y.datefrom <= X.dateto AND
prg.ppmpkey=x.ppmpkey AND
gya1.gyapkey = x.gyapkey AND
gya2.gyapkey = x.PZC0GYAKEY
AND dup.ppmpkey=x.ppmpkey
AND dup.gyapkey=x.gyapkey
AND gya1.gyacustnr<>gya2.gyacustnr
-- AND prg.ppmpkey='0000000B7OM'
ORDER BY 2 , 3 , 6