eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_PURGE dependencies on HZ_PURGE_CANDIDATES

Line 372: /* Insert into the hz_purge_candidates table */

368: end if;
369:
370: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, false, conc_prg, regid_flag);
371:
372: /* Insert into the hz_purge_candidates table */
373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
374: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
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,

Line 373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,

369:
370: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, false, conc_prg, regid_flag);
371:
372: /* Insert into the hz_purge_candidates table */
373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
374: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
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',

Line 393: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,

389: loop
390: FETCH purge_parties into pid;
391: exit when purge_parties%NOTFOUND;
392: i:=i+1;
393: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
394: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
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',

Line 555: select candidate_party_id, party_name from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED';

551: l_tmp VARCHAR2(2000);
552:
553:
554: cursor printparties(batchId number) is
555: select candidate_party_id, party_name from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED';
556:
557: BEGIN
558: retcode:=0;
559:

Line 585: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

581:
582: hz_common_pub.disable_cont_source_security;
583: --4307686
584: DELETE from HZ_PARTY_USG_ASSIGNMENTS where PARTY_ID in
585: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
586: log(' HZ_PARTY_USG_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
587:
588: DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID in
589: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 589: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

585: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
586: log(' HZ_PARTY_USG_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
587:
588: DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID in
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

Line 595: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')

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);
598: begin
599: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in

Line 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;

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;

Line 611: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')) and OWNER_TABLE_NAME='HZ_PARTY_SITES';

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:
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 (

Line 617: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')));

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
617: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')));
618: log(' HZ_ORG_CONTACT_ROLES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
619:
620: begin
621: execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in

Line 624: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')))' using batchid;

620: begin
621: execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in
622: ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in
623: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
624: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')))' using batchid;
625: log(' HZ_STAGED_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
626: exception
627: when others then
628: null;

Line 633: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

629: end;
630:
631: DELETE from HZ_ORG_CONTACTS where PARTY_SITE_ID in
632: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
633: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
634: log(' HZ_ORG_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
635:
636: DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
637: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in

Line 638: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

634: log(' HZ_ORG_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
635:
636: DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
637: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
638: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
639: log(' HZ_PARTY_SITE_USES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
640:
641: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
642: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in

Line 643: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))

639: log(' HZ_PARTY_SITE_USES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
640:
641: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
642: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
643: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))
644: and OWNER_TABLE_NAME=''HZ_PARTY_SITES''' using batchid;
645: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
646:
647: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in

Line 649: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))

645: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
646:
647: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
648: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
649: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))
650: and CONTACT_LEVEL_TABLE=''HZ_PARTY_SITES''' using batchid;
651: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
652:
653: begin

Line 656: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))' using batchid;

652:
653: begin
654: execute immediate 'DELETE from HZ_STAGED_PARTY_SITES where PARTY_SITE_ID in
655: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
656: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))' using batchid;
657: log(' HZ_STAGED_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
658: exception
659: when others then
660: null;

Line 664: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

660: null;
661: end;
662:
663: DELETE 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

Line 669: (select candidate_party_id from hz_purge_candidates where batch_id=:1 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:
673: begin

Line 676: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

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

Line 685: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')

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:
689: DELETE from HZ_PERSON_PROFILES where PARTY_ID in

Line 690: (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);
692:
693: DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID in
694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

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);
692:
693: DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID in
694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
695: log(' HZ_FINANCIAL_PROFILE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
696:
697: DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID in
698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
695: log(' HZ_FINANCIAL_PROFILE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
696:
697: DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID in
698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
699: log(' HZ_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
700:
701: DELETE from HZ_CERTIFICATIONS where PARTY_ID in
702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
699: log(' HZ_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
700:
701: DELETE from HZ_CERTIFICATIONS where PARTY_ID in
702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
703: log(' HZ_CERTIFICATIONS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
704: DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
706: log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

701: DELETE from HZ_CERTIFICATIONS where PARTY_ID in
702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
703: log(' HZ_CERTIFICATIONS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
704: DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
706: log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
707: DELETE from HZ_SECURITY_ISSUED where PARTY_ID in
708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
709: log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

704: DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
706: log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
707: DELETE from HZ_SECURITY_ISSUED where PARTY_ID in
708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
709: log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
710: DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
711: (select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

Line 712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
709: log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
710: DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
711: (select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
713: log(' HZ_FINANCIAL_NUMBERS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
714: DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
716: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

711: (select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
713: log(' HZ_FINANCIAL_NUMBERS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
714: DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
716: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
717: DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID in
718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
719: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

714: DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
716: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
717: DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID in
718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
719: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
720:
721: DELETE from HZ_PERSON_INTEREST where PARTY_ID in
722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
719: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
720:
721: DELETE from HZ_PERSON_INTEREST where PARTY_ID in
722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
723: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
724:
725: DELETE from HZ_CITIZENSHIP where PARTY_ID in
726: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 726: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
723: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
724:
725: DELETE from HZ_CITIZENSHIP where PARTY_ID in
726: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
727: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
728:
729: DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
730: (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in

Line 731: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

727: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
728:
729: DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
730: (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in
731: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
732: log(' HZ_WORK_CLASS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
733: DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
735: log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

730: (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in
731: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
732: log(' HZ_WORK_CLASS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
733: DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
735: log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
736: DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
738: log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

733: DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
735: log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
736: DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
738: log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
739: DELETE from HZ_EDUCATION where PARTY_ID in
740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
741: log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

736: DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
738: log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
739: DELETE from HZ_EDUCATION where PARTY_ID in
740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
741: log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
742: DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
744: log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

739: DELETE from HZ_EDUCATION where PARTY_ID in
740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
741: log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
742: DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
744: log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
745: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
747: and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;

Line 746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

742: DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
744: log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
745: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
747: and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;
748: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
749: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

Line 750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
747: and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;
748: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
749: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
751: and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using batchid;
752: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
753: execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id in
754: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;

Line 754: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;

750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
751: and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using batchid;
752: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
753: execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id in
754: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;
755: log(' HZ_ORIG_SYS_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
756:
757: begin
758: execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID in

Line 759: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')' using batchid;

755: log(' HZ_ORIG_SYS_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
756:
757: begin
758: execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID in
759: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')' using batchid;
760: log(' HZ_STAGED_PARTIES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
761: exception
762: when others then
763: null;

Line 767: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

763: null;
764: end;
765:
766: DELETE from AS_CHANGED_ACCOUNTS_ALL where CUSTOMER_ID in
767: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
768: log(' AS_CHANGED_ACCOUNTS_ALL : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
769:
770:
771: delete from wsh_location_owners wlo

Line 773: from hz_purge_candidates

769:
770:
771: delete from wsh_location_owners wlo
772: where wlo.owner_party_id in (select candidate_party_id
773: from hz_purge_candidates
774: where batch_id=batchid and status='IDENTIFIED')
775: and exists (
776: select 'x'
777: from wsh_location_owners wlo1

Line 783: from hz_purge_candidates

779:
780: update wsh_location_owners wlo
781: set wlo.owner_party_id = -1
782: where wlo.owner_party_id in (select candidate_party_id
783: from hz_purge_candidates
784: where batch_id=batchid and status='IDENTIFIED'
785: and rownum = 1 )-- if more than one party has same location, only update one.
786: and not exists (
787: select 'x'

Line 796: from hz_purge_candidates

792: -- make sure to keep the ones owner_party_id = -1
793:
794: delete from wsh_location_owners wlo
795: where wlo.owner_party_id in (select candidate_party_id
796: from hz_purge_candidates
797: where batch_id=batchid and status='IDENTIFIED')
798: and wlo.owner_party_id <> -1;
799:
800: Delete from zx_party_tax_profile PTP

Line 803: from hz_purge_candidates

799:
800: Delete from zx_party_tax_profile PTP
801: where ptp.party_type_code = 'THIRD_PARTY'
802: and ptp.party_id in (select candidate_party_id
803: from hz_purge_candidates
804: where batch_id=batchid and status='IDENTIFIED')
805: and not exists (Select 'x'
806: from zx_registrations reg
807: where ptp.party_tax_profile_id = reg.party_tax_profile_id)

Line 821: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

817: where HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
818: AND HCA.OWNER_TABLE_ID = PTP.PARTY_TAX_PROFILE_ID);
819:
820: DELETE from HZ_PARTIES where party_id in
821: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
822: log(' HZ_PARTIES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
823: fnd_file.close;
824:
825: --if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null

Line 828: (select pur_cand.candidate_party_id from hz_purge_candidates pur_cand, hz_parties parties where pur_cand.batch_id=:1 and

824:
825: --if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null
826:
827: execute immediate 'update hz_relationships set party_id=null where party_id in
828: (select pur_cand.candidate_party_id from hz_purge_candidates pur_cand, hz_parties parties where pur_cand.batch_id=:1 and
829: pur_cand.candidate_party_id = parties.party_id and parties.party_type=''PARTY_RELATIONSHIP'' )' using batchid;
830:
831:
832: delete from hz_parties where party_id in (select party_id from hz_relationships

Line 834: from hz_purge_candidates

830:
831:
832: delete from hz_parties where party_id in (select party_id from hz_relationships
833: where (subject_id in (select candidate_party_id
834: from hz_purge_candidates
835: where batch_id=batchid and status='IDENTIFIED')
836: or object_id in (select candidate_party_id
837: from hz_purge_candidates
838: where batch_id=batchid and status='IDENTIFIED')))

Line 837: from hz_purge_candidates

833: where (subject_id in (select candidate_party_id
834: from hz_purge_candidates
835: where batch_id=batchid and status='IDENTIFIED')
836: or object_id in (select candidate_party_id
837: from hz_purge_candidates
838: where batch_id=batchid and status='IDENTIFIED')))
839: and status = 'M';
840:
841:

Line 846: from hz_purge_candidates

842:
843: -- bug 4947069
844:
845: delete from hz_relationships where (subject_id in (select candidate_party_id
846: from hz_purge_candidates
847: where batch_id=batchid and status='IDENTIFIED')
848: or object_id in (select candidate_party_id
849: from hz_purge_candidates
850: where batch_id=batchid and status='IDENTIFIED'))

Line 849: from hz_purge_candidates

845: delete from hz_relationships where (subject_id in (select candidate_party_id
846: from hz_purge_candidates
847: where batch_id=batchid and status='IDENTIFIED')
848: or object_id in (select candidate_party_id
849: from hz_purge_candidates
850: where batch_id=batchid and status='IDENTIFIED'))
851: and status = 'M';
852:
853:

Line 855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'

851: and status = 'M';
852:
853:
854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;

Line 856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';

852:
853:
854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:

Line 857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (

853:
854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:
861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */

Line 858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')

854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:
861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */
862: update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;

Line 907: select count(*) from hz_purge_candidates where candidate_party_id=p_id and status<>'PURGED';

903: p_party_id NUMBER) IS
904:
905: insertrows number;
906: cursor existing_id(p_id number) is
907: select count(*) from hz_purge_candidates where candidate_party_id=p_id and status<>'PURGED';
908:
909: BEGIN
910:
911: SAVEPOINT PURGE_PARTY;

Line 923: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,

919: x_return_status := FND_API.G_RET_STS_SUCCESS;
920: ---please enter the directory as the third parameter to which the file needs to be copied.
921: --fnd_file.put_names('delparty.log',null,'/sqlcom/outbound');
922:
923: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
924: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
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',

Line 1105: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */

1101:
1102: delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';
1103:
1104:
1105: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
1106: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;
1107:
1108: --fnd_file.close;
1109: hz_common_pub.enable_cont_source_security;

Line 1106: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;

1102: delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';
1103:
1104:
1105: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
1106: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;
1107:
1108: --fnd_file.close;
1109: hz_common_pub.enable_cont_source_security;
1110: -- standard call to get message count and if count is 1, get message info.