[Home] [Help]
650: l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
651:
652: IF P_BATCH_MODE_FLAG = 'Y' THEN
653: execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_PARTIES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
654: execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_ADDRESSES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
655: execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTPTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
656: execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CREDITRTNGS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
657: execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CLASSIFICS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
658: execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_FINREPORTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
665: ELSE
666: DELETE HZ_IMP_PARTIES_SG
667: WHERE batch_id = P_BATCH_ID
668: AND batch_mode_flag = P_BATCH_MODE_FLAG;
669: DELETE HZ_IMP_ADDRESSES_SG
670: WHERE batch_id = P_BATCH_ID
671: AND batch_mode_flag = P_BATCH_MODE_FLAG;
672: DELETE HZ_IMP_CONTACTPTS_SG
673: WHERE batch_id = P_BATCH_ID
728: ) IS
729:
730: l_cleanup_ssm_site_qry VARCHAR2(4000) :=
731: ' SELECT psosr.rowid,posr.owner_table_id
732: FROM HZ_IMP_ADDRESSES_SG site_sg,
733: HZ_ORIG_SYS_REFERENCES posr,
734: HZ_ORIG_SYS_REFERENCES psosr
735: WHERE site_sg.batch_id = :CP_BATCH_ID
736: AND site_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
781:
782:
783: l_cleanup_ssm_add_dnb VARCHAR2(4000) :=
784: ' select hos.rowid, hps_old.identifying_Address_flag, hps_old.rowid,hps_new.rowid
785: from hz_imp_addresses_sg has, hz_orig_sys_references hos, hz_party_sites hps_new,hz_party_sites hps_old
786: where has.site_orig_system=''DNB''
787: and has.old_site_orig_system_ref=hos.orig_system_reference
788: and hos.orig_system=has.site_orig_system
789: and hos.owner_table_name=''HZ_PARTY_SITES''
986: l_rowid L_ROWIDList;
987: l_pid L_PIDList;
988: l_pos L_POSList;
989: l_posr L_POSRList;
990: TYPE L_PSOSList IS TABLE OF hz_imp_addresses_sg.SITE_ORIG_SYSTEM%TYPE;
991: TYPE L_PSOSRList IS TABLE OF hz_imp_addresses_sg.SITE_ORIG_SYSTEM_REFERENCE%TYPE;
992: l_psos L_PSOSList;
993: l_psosr L_PSOSRList;
994:
987: l_pid L_PIDList;
988: l_pos L_POSList;
989: l_posr L_POSRList;
990: TYPE L_PSOSList IS TABLE OF hz_imp_addresses_sg.SITE_ORIG_SYSTEM%TYPE;
991: TYPE L_PSOSRList IS TABLE OF hz_imp_addresses_sg.SITE_ORIG_SYSTEM_REFERENCE%TYPE;
992: l_psos L_PSOSList;
993: l_psosr L_PSOSRList;
994:
995: rows NUMBER;
1013: SELECT ROW_ID,site_os, site_osr
1014: FROM
1015: ( SELECT int_row_id row_id,SITE_ORIG_SYSTEM site_os,SITE_ORIG_SYSTEM_REFERENCE site_osr
1016: ,row_number() over (partition by party_site_id order by int_row_id) rn
1017: from hz_imp_addresses_sg
1018: where batch_id=P_BATCH_ID
1019: and party_orig_system = P_PARTY_OS
1020: and action_flag = 'U'
1021: )
4046: IF P_BATCH_MODE_FLAG = 'Y' THEN
4047: -- Analyze staging table after matching
4048: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Analyze other entity staging tables ' ||
4049: to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
4050: fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSES_SG', percent=>5, degree=>4);
4051: fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSUSES_SG', percent=>5, degree=>4);
4052: fnd_stats.gather_table_stats('AR', 'HZ_IMP_CLASSIFICS_SG', percent=>5, degree=>4);
4053: fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTPTS_SG', percent=>5, degree=>4);
4054: fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTROLES_SG', percent=>5, degree=>4);
4672: AND batch_mode_flag = p_batch_mode_flag
4673: AND rownum = 1)
4674: OR EXISTS (
4675: SELECT 'Y'
4676: FROM HZ_IMP_ADDRESSES_SG
4677: WHERE batch_id = p_batch_id
4678: AND batch_mode_flag = p_batch_mode_flag
4679: AND rownum = 1)
4680: OR EXISTS (
4737: SELECT 'Y'
4738: FROM dual
4739: WHERE EXISTS (
4740: SELECT 'Y'
4741: FROM HZ_IMP_ADDRESSES_SG
4742: WHERE batch_id = p_batch_id
4743: AND batch_mode_flag = p_batch_mode_flag
4744: AND rownum = 1)
4745: OR EXISTS (