525: x_insert_threshold number := 60;
526: l_party_limit NUMBER := 50000;
527: l_detail_limit NUMBER := 100000;
528: BEGIN
529: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
530: insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score)
531: select f, t, least(f,t), greatest(f,t), sum(score) score from (
532: select /*+ ORDERED */ s1.party_id f, s2.party_id t,
533: -------PARTY ENTITY: SCORING SECTION ---------
602: group by f, t
603: having sum(score) >= x_insert_threshold
604: ;
605: inserted_duplicates := (SQL%ROWCOUNT);
606: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
607: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
608: FND_CONCURRENT.AF_Commit;
609:
610:
603: having sum(score) >= x_insert_threshold
604: ;
605: inserted_duplicates := (SQL%ROWCOUNT);
606: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
607: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
608: FND_CONCURRENT.AF_Commit;
609:
610:
611: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
607: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
608: FND_CONCURRENT.AF_Commit;
609:
610:
611: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
612: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
613: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
614: open x_ent_cur for
615: select f, t, max(score) score from (
608: FND_CONCURRENT.AF_Commit;
609:
610:
611: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
612: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
613: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
614: open x_ent_cur for
615: select f, t, max(score) score from (
616: select /*+ ORDERED */ s1.party_id f, s2.party_id t,
609:
610:
611: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
612: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
613: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
614: open x_ent_cur for
615: select f, t, max(score) score from (
616: select /*+ ORDERED */ s1.party_id f, s2.party_id t,
617: decode(instrb(s2.TX26,s1.TX26),1,100,
647: )
648: ) group by f,t ;
649: HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);
650: close x_ent_cur;
651: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
652: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
653: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
654: FND_CONCURRENT.AF_Commit;
655:
648: ) group by f,t ;
649: HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);
650: close x_ent_cur;
651: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
652: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
653: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
654: FND_CONCURRENT.AF_Commit;
655:
656:
649: HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);
650: close x_ent_cur;
651: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
652: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
653: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
654: FND_CONCURRENT.AF_Commit;
655:
656:
657: ---------- exception block ---------------
680: IS
681: x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
682: x_insert_threshold number := 60;
683: BEGIN
684: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
685: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
686: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
687: insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag
688: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
681: x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
682: x_insert_threshold number := 60;
683: BEGIN
684: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
685: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
686: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
687: insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag
688: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
689: select f, t, sum(score) sc, party_osr, party_os, p_batch_id, 'N'
682: x_insert_threshold number := 60;
683: BEGIN
684: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
685: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
686: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
687: insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag
688: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
689: select f, t, sum(score) sc, party_osr, party_os, p_batch_id, 'N'
690: ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
763: )
764: group by f, t, party_osr, party_os
765: having sum(score) >= x_insert_threshold
766: ;
767: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
768: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
769:
770:
771: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
764: group by f, t, party_osr, party_os
765: having sum(score) >= x_insert_threshold
766: ;
767: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
768: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
769:
770:
771: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
772: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
767: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
768: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
769:
770:
771: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
772: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
773: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
774: open x_ent_cur for
775: select f,t,max(score) from (
768: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
769:
770:
771: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
772: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
773: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
774: open x_ent_cur for
775: select f,t,max(score) from (
776: select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
769:
770:
771: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
772: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
773: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
774: open x_ent_cur for
775: select f,t,max(score) from (
776: select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
777: decode(instrb(s2.TX26,s1.TX26),1,100,
808: )
809: ) group by f,t ;
810: HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);
811: close x_ent_cur;
812: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
813: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
814: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
815:
816: --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
809: ) group by f,t ;
810: HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);
811: close x_ent_cur;
812: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
813: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
814: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
815:
816: --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
817:
810: HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);
811: close x_ent_cur;
812: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
813: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
814: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
815:
816: --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
817:
818: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
814: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
815:
816: --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
817:
818: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
819: FND_FILE.put_line(FND_FILE.log,'DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY ');
820: FND_FILE.put_line(FND_FILE.log,'Begin time to delete '||to_char(sysdate,'hh24:mi:ss'));
821:
822: delete from hz_imp_dup_parties a
815:
816: --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
817:
818: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
819: FND_FILE.put_line(FND_FILE.log,'DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY ');
820: FND_FILE.put_line(FND_FILE.log,'Begin time to delete '||to_char(sysdate,'hh24:mi:ss'));
821:
822: delete from hz_imp_dup_parties a
823: where (a.party_osr >= from_osr and a.party_osr <= to_osr
816: --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
817:
818: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
819: FND_FILE.put_line(FND_FILE.log,'DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY ');
820: FND_FILE.put_line(FND_FILE.log,'Begin time to delete '||to_char(sysdate,'hh24:mi:ss'));
821:
822: delete from hz_imp_dup_parties a
823: where (a.party_osr >= from_osr and a.party_osr <= to_osr
824: and a.batch_id = p_batch_id)
835: (Select 1 from hz_imp_dup_parties b
836: where b.batch_id=p_batch_id and a.party_id=b.party_id and a.score < b.score)
837: );
838:
839: FND_FILE.put_line(FND_FILE.log,'Number of records deleted from hz_imp_dup_parties '||SQL%ROWCOUNT);
840: FND_FILE.put_line(FND_FILE.log,'End time to delete '||to_char(sysdate,'hh24:mi:ss'));
841: --------UPDATE AUTO MERGE FLAG --------------
842: update hz_imp_dup_parties a
843: set a.auto_merge_flag = 'Y'
836: where b.batch_id=p_batch_id and a.party_id=b.party_id and a.score < b.score)
837: );
838:
839: FND_FILE.put_line(FND_FILE.log,'Number of records deleted from hz_imp_dup_parties '||SQL%ROWCOUNT);
840: FND_FILE.put_line(FND_FILE.log,'End time to delete '||to_char(sysdate,'hh24:mi:ss'));
841: --------UPDATE AUTO MERGE FLAG --------------
842: update hz_imp_dup_parties a
843: set a.auto_merge_flag = 'Y'
844: where a.score >= p_auto_merge_threshold
855: ----------------------PARTY LEVEL DUPLICATE IDENTIFICATION ENDS --------------------
856:
857:
858: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
859: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
860: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
861: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
862: insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
863: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
856:
857:
858: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
859: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
860: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
861: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
862: insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
863: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
864: select /*+ USE_CONCAT */ s1.party_id f,
857:
858: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
859: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
860: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
861: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
862: insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
863: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
864: select /*+ USE_CONCAT */ s1.party_id f,
865: decode(instrb(s2.TX5,s1.TX5),1,60,
900: where a.batch_id = p_batch_id
901: and a.party_osr between from_osr and to_osr and a.entity ='CONTACT_POINTS';
902: HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACT_POINTS',p_batch_id, x_ent_cur);
903: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
904: FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
905: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
906: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
907:
908:
901: and a.party_osr between from_osr and to_osr and a.entity ='CONTACT_POINTS';
902: HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACT_POINTS',p_batch_id, x_ent_cur);
903: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
904: FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
905: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
906: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
907:
908:
909:
902: HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACT_POINTS',p_batch_id, x_ent_cur);
903: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
904: FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
905: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
906: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
907:
908:
909:
910: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
907:
908:
909:
910: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
911: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
912: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
913: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
914: insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
915: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
908:
909:
910: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
911: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
912: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
913: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
914: insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
915: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
916: select /*+ USE_CONCAT */ s1.party_id f,
909:
910: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
911: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
912: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
913: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
914: insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
915: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
916: select /*+ USE_CONCAT */ s1.party_id f,
917: decode(instrb(s2.TX26,s1.TX26),1,100,
959: where a.batch_id = p_batch_id
960: and a.party_osr between from_osr and to_osr and a.entity ='PARTY_SITES';
961: HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('PARTY_SITES',p_batch_id, x_ent_cur);
962: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
963: FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
964: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
965: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
966:
967:
960: and a.party_osr between from_osr and to_osr and a.entity ='PARTY_SITES';
961: HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('PARTY_SITES',p_batch_id, x_ent_cur);
962: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
963: FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
964: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
965: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
966:
967:
968:
961: HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('PARTY_SITES',p_batch_id, x_ent_cur);
962: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
963: FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
964: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
965: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
966:
967:
968:
969: ---------- exception block ---------------
987: IS
988: x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
989: x_insert_threshold number := 60;
990: BEGIN
991: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
992: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
993: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
994: insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
995: select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
988: x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
989: x_insert_threshold number := 60;
990: BEGIN
991: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
992: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
993: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
994: insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
995: select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
996: ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
989: x_insert_threshold number := 60;
990: BEGIN
991: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
992: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
993: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
994: insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
995: select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
996: ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
997: select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1067: )
1068: group by f, t, fos, tos
1069: having sum(score) >= x_insert_threshold
1070: ;
1071: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
1072: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1073: FND_CONCURRENT.AF_Commit;
1074:
1075:
1068: group by f, t, fos, tos
1069: having sum(score) >= x_insert_threshold
1070: ;
1071: FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
1072: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1073: FND_CONCURRENT.AF_Commit;
1074:
1075:
1076: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1072: FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1073: FND_CONCURRENT.AF_Commit;
1074:
1075:
1076: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1077: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
1078: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1079: open x_ent_cur for
1080: select f,t,max(score) from (
1073: FND_CONCURRENT.AF_Commit;
1074:
1075:
1076: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1077: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
1078: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1079: open x_ent_cur for
1080: select f,t,max(score) from (
1081: select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1074:
1075:
1076: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1077: FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
1078: FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1079: open x_ent_cur for
1080: select f,t,max(score) from (
1081: select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1082: decode(instrb(s2.TX26,s1.TX26),1,100,
1114: )
1115: ) group by f,t ;
1116: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1117: close x_ent_cur;
1118: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1119: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1120: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1121: FND_CONCURRENT.AF_Commit;
1122: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1115: ) group by f,t ;
1116: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1117: close x_ent_cur;
1118: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1119: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1120: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1121: FND_CONCURRENT.AF_Commit;
1122: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1123: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1116: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1117: close x_ent_cur;
1118: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1119: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1120: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1121: FND_CONCURRENT.AF_Commit;
1122: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1123: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1124: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1119: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1120: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1121: FND_CONCURRENT.AF_Commit;
1122: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1123: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1124: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1125: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1126: insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1127: dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1120: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1121: FND_CONCURRENT.AF_Commit;
1122: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1123: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1124: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1125: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1126: insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1127: dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1128: dup_creation_date,dup_last_update_date
1121: FND_CONCURRENT.AF_Commit;
1122: -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1123: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1124: FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1125: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1126: insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1127: dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1128: dup_creation_date,dup_last_update_date
1129: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1159: (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
1160: )
1161: )
1162: ;
1163: FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
1164: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1165: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1166: FND_CONCURRENT.AF_Commit;
1167: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1160: )
1161: )
1162: ;
1163: FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
1164: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1165: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1166: FND_CONCURRENT.AF_Commit;
1167: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1168: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1161: )
1162: ;
1163: FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
1164: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1165: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1166: FND_CONCURRENT.AF_Commit;
1167: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1168: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1169: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1164: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1165: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1166: FND_CONCURRENT.AF_Commit;
1167: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1168: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1169: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1170: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1171: insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1172: dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1165: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1166: FND_CONCURRENT.AF_Commit;
1167: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1168: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1169: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1170: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1171: insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1172: dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1173: dup_creation_date,dup_last_update_date
1166: FND_CONCURRENT.AF_Commit;
1167: -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1168: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1169: FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1170: FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1171: insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1172: dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1173: dup_creation_date,dup_last_update_date
1174: ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1210: (
1211: ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1212: )
1213: ;
1214: FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
1215: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1216: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1217: FND_CONCURRENT.AF_Commit;
1218:
1211: ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1212: )
1213: ;
1214: FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
1215: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1216: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1217: FND_CONCURRENT.AF_Commit;
1218:
1219: ---------- exception block ---------------
1212: )
1213: ;
1214: FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
1215: FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1216: FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1217: FND_CONCURRENT.AF_Commit;
1218:
1219: ---------- exception block ---------------
1220: EXCEPTION