[Home] [Help]
12: and nvl(validate_purge_flag,'Y') <> 'N'; --5125968
13: --and entity_name in (select table_name from fnd_tables);
14:
15: cursor x1(app_id number) is --4500011
16: select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name, decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
17: decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
18: AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
19: from hz_merge_dictionary where parent_entity_name like 'HZ_%' and dict_application_id = app_id
20: and fk_column_name IS NOT NULL and entity_name not in ('AS_CHANGED_ACCOUNTS_ALL','POS_PARTIES_V','POS_PARTY_SITES_V','WSH_LOCATION_OWNERS','ZX_PARTY_TAX_PROFILE','CE_BANKS_MERGE_V','CE_BANK_BRANCHES_MERGE_V','WSH_SUPPLIER_SF_SITES_V')
20: and fk_column_name IS NOT NULL and entity_name not in ('AS_CHANGED_ACCOUNTS_ALL','POS_PARTIES_V','POS_PARTY_SITES_V','WSH_LOCATION_OWNERS','ZX_PARTY_TAX_PROFILE','CE_BANKS_MERGE_V','CE_BANK_BRANCHES_MERGE_V','WSH_SUPPLIER_SF_SITES_V')
21: and nvl(validate_purge_flag,'Y') <> 'N'; --5125968
22:
23: cursor x4(app_id number) is --4500011
24: select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
25: decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
26: decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
27: AND directional_flag = ''F''', join_clause) join_clause,
28: parent_entity_name, fk_data_type
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
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;
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
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: -- bug 4947069
829: pur_cand.candidate_party_id = parties.party_id and parties.party_type=''PARTY_RELATIONSHIP'' )' using batchid;
830:
831: -- bug 4947069
832:
833: delete from hz_relationships 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
1076: HZ_BES_BO_UTIL_PKG.del_obj_hierarchy(p_party_id);
1077:
1078: END IF;
1079:
1080: /* if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null */
1081: execute immediate 'update hz_relationships set party_id=null where party_id=:1 and party_id in
1082: (select party_id from hz_parties where party_type = ''PARTY_RELATIONSHIP'')' using p_party_id;
1083:
1084: -- bug 4947069
1077:
1078: END IF;
1079:
1080: /* if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null */
1081: execute immediate 'update hz_relationships set party_id=null where party_id=:1 and party_id in
1082: (select party_id from hz_parties where party_type = ''PARTY_RELATIONSHIP'')' using p_party_id;
1083:
1084: -- bug 4947069
1085:
1082: (select party_id from hz_parties where party_type = ''PARTY_RELATIONSHIP'')' using p_party_id;
1083:
1084: -- bug 4947069
1085:
1086: delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';
1087:
1088: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
1089: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;
1090:
1436: cnt2 NUMBER := 0;
1437: cnt3 NUMBER := 0;
1438:
1439: cursor x2(parent varchar2) is --4500011
1440: select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
1441: decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
1442: decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
1443: AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
1444: from hz_merge_dictionary where entity_name = parent;
1443: AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
1444: from hz_merge_dictionary where entity_name = parent;
1445:
1446: cursor x3(parent2 varchar2) is --4500011
1447: select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
1448: decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
1449: decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
1450: AND directional_flag = ''F''', join_clause) join_clause,
1451: parent_entity_name,fk_data_type