AR Import Batch De-Duplication

SELECT DECODE(PARTY_TYPE,'PERSON',pint.PERSON_FIRST_NAME||' '||pint.PERSON_LAST_NAME,pint.ORGANIZATION_NAME)CPTS_PARTY_NAME,
        (CASE WHEN cint.contact_point_type IN 
				     ('FAX','GEN','HOME','MOBILE','OFFICE','PAGER','VM', 'SMS')
             THEN NVL(cint.raw_phone_number,DECODE(PHONE_AREA_CODE,NULL,PHONE_NUMBER,PHONE_AREA_CODE||'-'||PHONE_NUMBER))
             WHEN cint.contact_point_type = 'EMAIL' THEN cint.email_address
             WHEN cint.contact_point_type = 'EFT' THEN cint.eft_user_number
             WHEN cint.contact_point_type = 'EDI' THEN cint.edi_id_number
             WHEN cint.contact_point_type = 'WEB' THEN cint.url             
             WHEN cint.contact_point_type = 'TLX' THEN cint.telex_number
             WHEN cint.contact_point_type IS NULL THEN cint.raw_phone_number
	     ELSE cint.raw_phone_number
	END) CONTACT_POINT,
        lkp.MEANING CONTACT_POINT_TYPE,
        dedup.WINNER_RECORD_OSR CPTS_WINNER_RECORD_OSR,
	dedup.DUP_RECORD_OSR CPTS_SOURCE_SYSTEM_ID,
        dedup.DETAIL_PARTY_OSR CPTS_PARTY_SOURCE_SYSTEM_ID,
        HZ_BATCH_ACTION_PUB.GET_DEDUP_BATCH_STATUS(bat.BATCH_ID,dedup.ENTITY,bat.BD_ACTION_ON_CONTACT_POINTS,dedup.WINNER_RECORD_OS,dedup.WINNER_RECORD_OSR,dedup.DUP_RECORD_OS,dedup.DUP_RECORD_OSR)  CPTS_DEDUP_STATUS
FROM   HZ_IMP_BATCH_SUMMARY      bat,
       HZ_IMP_INT_DEDUP_RESULTS  dedup,
       HZ_IMP_CONTACTPTS_INT     cint,
       HZ_IMP_PARTIES_INT        pint,
       FND_LOOKUP_VALUES         lkp
WHERE  bat.BATCH_ID=:P_BATCH_ID
AND    bat.ORIGINAL_SYSTEM=:P_ORIG_SYS
AND    dedup.ENTITY='CONTACT_POINTS'
AND    bat.BATCH_ID=dedup.BATCH_ID
AND    bat.ORIGINAL_SYSTEM=dedup.DUP_RECORD_OS     
AND    dedup.BATCH_ID=cint.BATCH_ID 
AND    dedup.WINNER_RECORD_OS=cint.CP_ORIG_SYSTEM
AND    dedup.DUP_RECORD_OS=cint.CP_ORIG_SYSTEM
AND    dedup.DUP_RECORD_OSR=cint.CP_ORIG_SYSTEM_REFERENCE
AND    dedup.DETAIL_PARTY_OS=cint.PARTY_ORIG_SYSTEM
AND    dedup.DETAIL_PARTY_OSR=cint.PARTY_ORIG_SYSTEM_REFERENCE
AND    cint.BATCH_ID=pint.BATCH_ID
AND    cint.PARTY_ORIG_SYSTEM=pint.PARTY_ORIG_SYSTEM
AND    cint.PARTY_ORIG_SYSTEM_REFERENCE=pint.PARTY_ORIG_SYSTEM_REFERENCE 
AND    lkp.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND    lkp.LOOKUP_CODE(+) = cint.CONTACT_POINT_TYPE
AND    lkp.LANGUAGE (+) = USERENV('LANG')
AND    lkp.VIEW_APPLICATION_ID (+) = 222
AND    lkp.SECURITY_GROUP_ID (+) =fnd_global.lookup_security_group('COMMUNICATION_TYPE', 222) 
ORDER BY CPTS_DEDUP_STATUS