375: select to_number(batchid), a.party_id, substr(a.party_name,1,250), a.party_number,
376: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
377: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
378: sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
379: from hz_parties a , hz_purge_gt temp, hz_contact_points cp where
380: temp.party_id = a.party_id and
381: cp.owner_table_id(+)=temp.party_id and
382: cp.contact_point_type(+)='PHONE' and
383: cp.owner_table_name(+)='HZ_PARTIES' and
395: select to_number(batchid), pid, substr(a.party_name,1,250), a.party_number,
396: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
397: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
398: sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
399: from hz_parties a , hz_contact_points cp where
400: a.party_id = pid and
401: cp.owner_table_id(+)= a.party_id and
402: cp.contact_point_type(+)='PHONE' and
403: cp.owner_table_name(+)='HZ_PARTIES' and
589: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
590: log(' HZ_ORGANIZATION_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
591:
592: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
593: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
594: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
595: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
596: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
597: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
592: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
593: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
594: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
595: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
596: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
597: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
598: begin
599: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
600: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
596: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
597: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
598: begin
599: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
600: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
601: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
602: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using batchid;
603: log(' HZ_STAGED_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows', conc_prg);
604: EXCEPTION
605: WHEN OTHERS THEN
606: null;
607: END;
608:
609: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
610: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
611: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')) and OWNER_TABLE_NAME='HZ_PARTY_SITES';
612: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
613:
608:
609: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
610: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
611: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')) and OWNER_TABLE_NAME='HZ_PARTY_SITES';
612: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
613:
614: DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
615: ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
616: SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
664: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
665: log(' HZ_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
666:
667: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
668: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
669: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
670: and OWNER_TABLE_NAME=''HZ_PARTIES'') and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
671: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
672:
666:
667: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
668: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
669: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
670: and OWNER_TABLE_NAME=''HZ_PARTIES'') and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
671: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
672:
673: begin
674: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
671: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
672:
673: begin
674: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
675: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
676: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
677: and OWNER_TABLE_NAME=''HZ_PARTIES'')' using batchid;
678: log(' HZ_STAGED_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
679: exception
680: when others then
681: null;
682: end;
683:
684: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
685: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')
686: and OWNER_TABLE_NAME='HZ_PARTIES';
687: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
688:
683:
684: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
685: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')
686: and OWNER_TABLE_NAME='HZ_PARTIES';
687: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
688:
689: DELETE from HZ_PERSON_PROFILES where PARTY_ID in
690: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
691: log(' HZ_PERSON_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
925: select to_number('-1'), a.party_id, substr(a.party_name,1,250), a.party_number,
926: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
927: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
928: sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
929: from hz_parties a , hz_contact_points cp where a.party_id = p_party_id and
930: cp.owner_table_id(+)=a.party_id and cp.contact_point_type(+)='PHONE' and cp.primary_flag(+)='Y' and
931: cp.owner_table_name(+)='HZ_PARTIES';
932:
933: hz_common_pub.disable_cont_source_security;
934:
935: DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID = p_party_id;
936:
937: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
938: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
939: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
940: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
941:
942: begin
936:
937: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
938: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
939: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
940: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
941:
942: begin
943: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
944: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
940: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
941:
942: begin
943: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
944: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
945: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using p_party_id;
946: exception
947: when others then
948: null;
947: when others then
948: null;
949: end;
950:
951: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
952: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id) and OWNER_TABLE_NAME='HZ_PARTY_SITES';
953:
954: DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
955: ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
986:
987: DELETE from HZ_PARTY_SITES where PARTY_ID = p_party_id;
988:
989: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
990: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')
991: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
992:
993: begin
994: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
987: DELETE from HZ_PARTY_SITES where PARTY_ID = p_party_id;
988:
989: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
990: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')
991: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
992:
993: begin
994: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
995: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')' using p_party_id;
991: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
992:
993: begin
994: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
995: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')' using p_party_id;
996: exception
997: when others then
998: null;
999: end;
997: when others then
998: null;
999: end;
1000:
1001: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID = p_party_id and OWNER_TABLE_NAME='HZ_PARTIES';
1002:
1003: DELETE from HZ_PERSON_PROFILES where PARTY_ID = p_party_id;
1004:
1005: DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID = p_party_id;