Reports

There is something ambiguos in the question - if we look your code

CASE WHEN v.status IN ('ACTIVE', 'PENDING') THEN 'ACTIVE' ELSE 'INACTIVE' END

... then it looks like you want the status column to be either 'ACTIVE' or 'INACTIVE'

If that is the case then one of the options is to use reverse logic - testing the 'INACTIVE' (or null) status and puting everything else as 'ACTIVE':

Select  Distinct
           c.owner_id, c.pet_id, c.name, c.address, 
           DECODE(Nvl(v.STATUS, 'INACTIVE'), 'INACTIVE', 'INACTIVE', 'ACTIVE')  as status 
From       CUSTOMERS_TABLE c
Left Join  VISITS_TABLE v ON(v.owner_id = c.owner_id And v.pet_id = c.pet_id)
Order By   c.owner_id, c.pet_id

OWNER_ID	PET_ID	NAME	ADDRESS	STATUS
1	1	Alice	1 The Street	ACTIVE
2	2	Beryl	2 The Road	ACTIVE
3	3	Carol	3 The Avenue	INACTIVE

fiddle

However, in your expected result there are three statuses (PAID, ACTIVE, INACTIVE) which is inconsistent with the code and raises the question of how many different statuses are there and how they should be treated regarding activity/inactivity - we know from the code about PENDING but there could be some other statuses too ?

79660243